from google.cloud.bigquery.client import Client
import google.auth
from google.cloud.bigquery.schema import SchemaField
from google.cloud import bigquery
import datetime, time
import json
from sklearn.preprocessing import MinMaxScaler
from sklearn.metrics import r2_score, mean_absolute_error, mean_squared_error
import json
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = 'connect_bigquery.json'
credentials, project = google.auth.default()
credentials = credentials.with_scopes(
['https://www.googleapis.com/auth/bigquery'])
client = Client(credentials=credentials)
dataset = client.dataset('ahamove_archive')
sql = """
select
accept_time,
board_time,
accept_distance as distance,
json_extract_scalar(_extra_props,'$.accept_lat') as accept_lat,
json_extract_scalar(_extra_props,'$.accept_lng') as accept_lng,
json_extract_scalar(_extra_props,'$.pickup_lat') as pickup_lat,
json_extract_scalar(_extra_props,'$.pickup_lng') as pickup_lng,
date_diff (date (o.order_time),date(s.first_complete_time),day) as experience
from ahamove_archive.order_archive o left join ahamove_archive.supplier s on o.supplier_id = s.id
where order_time >= '2019-03-01'
and order_time<='2019-03-02'
and o.status = 'COMPLETED'
and o.city_id = 'HAN'
features_time_signature = features[0]['time_signature']
features_valence = features[0]['valence']
# Fila a insertar en la tabla
rows_to_insert = [(artista,artist_followers,str(artist_genres),artist_id,artist_popularity,
album_type,album_genres,album_id,album_label,album_name,album_popularity,
album_release_date,album_realease_date_precision,track_name,str(track_avalible_markets),
str(track_explicit),track_id,track_popularity,track_number,features_acousticness,features_analysis_url,
features_danceability,features_duration_ms,features_energy,features_instrumentalness,features_key,features_liveness,
features_loudness,features_mode,features_speechiness,features_tempo,features_time_signature,features_valence)]
# Autenticación
os.environ[
'GOOGLE_APPLICATION_CREDENTIALS'] = '../datos/credenciales/MusicProjectTest-042a5c317e41.json'
# Conexion con la API de Big Query
big_query_client = Client()
# Se establece la BBDD
dataset_ref = big_query_client.dataset('musicData')
# establecemos la tabla
table_ref = dataset_ref.table('datos_spotify_{}'.format(letra_fichero))
#table_ref = dataset_ref.table('Test') #Para hacer pruebas de insercciones
table = big_query_client.get_table(table_ref)
# Insertar datos en Big Query
errors = big_query_client.insert_rows(table, rows_to_insert) # API request
for error in errors:
logging.error('Big Query: ' + error)
except Exception as e:
logging.error(e)
logging.error('-- Artista -> %s, album -> %s y cancion -> %s' %(artista, album_id, track_id))
except Exception as e:
logging.error(e)
logging.error('-- Artista -> %s y album -> %s' % (artista, album_id))
except Exception as e: