for connection in sakila_connections:
for table in sakila_tables:
extract = MySqlToGoogleCloudStorageOperator(
task_id="extract_mysql_%s_%s" % (connection, table),
mysql_conn_id=connection,
google_cloud_storage_conn_id='gcp_test',
sql="SELECT *, '%s' as source FROM sakila.%s" %
(connection, table),
bucket='ghen-airflow',
filename="%s/%s/%s{}.json" % (connection, table, table),
schema_filename="%s/schemas/%s.json" % (connection, table),
dag=dag)
load = GoogleCloudStorageToBigQueryOperator(
task_id="load_bq_%s_%s" % (connection, table),
bigquery_conn_id='gcp_test',
google_cloud_storage_conn_id='gcp_test',
bucket='ghen-airflow',
destination_project_dataset_table="spark-test-173322.%s.%s" %
(connection, table),
source_objects=["%s/%s/%s*.json" % (connection, table, table)],
schema_object="%s/schemas/%s.json" % (connection, table),
source_format='NEWLINE_DELIMITED_JSON',
create_disposition='CREATE_IF_NEEDED',
write_disposition='WRITE_TRUNCATE',
project_id='spark-test-173322',
dag=dag)
load.set_upstream(extract)
slack_notify.set_upstream(load)
try :
t5_prime_tableCheck=BigQueryCheckOperator(task_id='checkForTable',
sql="SELECT COUNT(*) FROM `{}.{}.{}`".format(project_id,dataset_id,table_id),
bigquery_conn_id=bigquery_conn_id,
use_legacy_sql=False,
dag=dag)
t5_prime_tableCheck.set_upstream(writeToGCS_task)
storageToBQ_task = GoogleCloudStorageToBigQueryOperator(task_id='uploadtoBQ_{}'.format(datetime.now().strptime('%Y%m%d_%H%M')),
bucket=bucket_name,
source_objects=tempGCS_dir_paths,
destination_project_dataset="`{}.{}.{}`".format(project_id,dataset_id,table_id),
schema_fields=schema,
create_disposition='WRITE_TRUNCATE',
dag=dag)
storageToBQ_task.set_upstream(t5_prime_tableCheck)
dummy_task.set_upstream(storageToBQ_task)
except Exception as e :
print("BigQueryCheck error = {}".format(e))
t5_gamme_tableCreate = BigQueryCreateExternalTableOperator(task_id='CreateBQtable',
bucket=bucket_name,
source_objects=tempGCS_dir_paths,
destination_project_dataset_table="{}.{}.{}"format(project_id,dataset_id,dataset_id),
schema=schema,
dag=dag)
dummy_task.set_upstream(t5_gamme_tableCreate)
'''
Read in queries stored in GCS bucket
end = DummyOperator(
task_id='end',
trigger_rule='one_success'
)
# Task to copy files from S3 to GCS
s3_email_to_gcs = S3ToGoogleCloudStorageOperator(
task_id='s3_to_gcs',
bucket= S3_BUCKET,
aws_conn_id=AWS_CONNECTION,
dest_gcs_conn_id= GCS_BUCKET_CONNECTION,
dest_gcs=GCS_BUCKET_NAME_S3_TASK
)
s3_email_to_gcs.set_upstream(start)
# Task to load files from GCS into BQ
gcs_to_bq_task = GoogleCloudStorageToBigQueryOperator(
task_id='gcs_to_bq',
bucket=GCS_BUCKET_NAME_BQ_TASK,
source_objects= ['*.parquet'], # Assumes we're loading parquet files, other file types are supported
destination_project_dataset_table=DESTINATION_TABLE,
source_format='parquet',
skip_leading_rows=1,
max_bad_records=10,
bigquery_conn_id= BQ_CONNECTION,
create_disposition='CREATE_IF_NEEDED', # Creates table in BQ if none is found
write_disposition='WRITE_APPEND'
)
gcs_to_bq_task.set_upstream(s3_email_to_gcs)