def test_description_external_table(self, bq_hook):
operator = GCSToBigQueryOperator(
task_id=TASK_ID,
bucket=TEST_BUCKET,
source_objects=TEST_SOURCE_OBJECTS,
destination_project_dataset_table=TEST_EXPLICIT_DEST,
description=DESCRIPTION,
external_table=True,
)
operator.execute(None)
# fmt: off
bq_hook.return_value.get_conn.return_value.cursor.return_value.create_external_table. \
assert_called_once_with(
external_project_dataset_table=mock.ANY,
schema_fields=mock.ANY,
source_uris=mock.ANY,
source_format=mock.ANY,
compression=mock.ANY,
skip_leading_rows=mock.ANY,
field_delimiter=mock.ANY,
max_bad_records=mock.ANY,
quote_character=mock.ANY,
ignore_unknown_values=mock.ANY,
allow_quoted_newlines=mock.ANY,
allow_jagged_rows=mock.ANY,
encoding=mock.ANY,
src_fmt_configs=mock.ANY,
encryption_configuration=mock.ANY,
labels=mock.ANY,
description=DESCRIPTION,
)
def test_execute_explicit_project(self, bq_hook):
operator = GCSToBigQueryOperator(
task_id=TASK_ID,
bucket=TEST_BUCKET,
source_objects=TEST_SOURCE_OBJECTS,
destination_project_dataset_table=TEST_EXPLICIT_DEST,
max_id_key=MAX_ID_KEY,
)
# using non-legacy SQL
bq_hook.return_value.get_conn.return_value.cursor.return_value.use_legacy_sql = False
operator.execute(None)
bq_hook.return_value.get_conn.return_value.cursor.return_value.execute.assert_called_once_with(
"SELECT MAX(id) FROM `test-project.dataset.table`")
def bs_customer_invoice_chinook_dag():
@task()
def extract_transform():
conn = sqlite3.connect(f"{DATA_PATH}/chinook.db")
with open(f"{BASE_PATH}/sql/chinook.sql", "r") as query:
df = pd.read_sql(query.read(), conn)
df.to_csv(OUT_PATH, index=False, header=False) #prevent on create Index column and exclude the header row
start = DummyOperator(task_id='start')
end = DummyOperator(task_id='end')
extracted_transformed_data = extract_transform()
stored_data_gcs = LocalFilesystemToGCSOperator(
task_id="store_to_gcs",
gcp_conn_id=GOOGLE_CLOUD_CONN_ID,
src=OUT_PATH,
dst=GCS_OBJECT_NAME,
bucket=BUCKET_NAME
)
loaded_data_bigquery = GCSToBigQueryOperator(
task_id='load_to_bigquery',
bigquery_conn_id=GOOGLE_CLOUD_CONN_ID,
bucket=BUCKET_NAME,
source_objects=[GCS_OBJECT_NAME],
destination_project_dataset_table=f"{DATASET_ID}.{BIGQUERY_TABLE_NAME}",
schema_fields=[ #based on https://cloud.google.com/bigquery/docs/schemas
{'name': 'customer_id', 'type': 'INT64', 'mode': 'REQUIRED'},
{'name': 'full_name', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'company', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'address', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'city', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'state', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'country', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'postal_code', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'phone', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'fax', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'email', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'invoice_id', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'invoice_date', 'type': 'DATE', 'mode': 'NULLABLE'},
{'name': 'billing_address', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'billing_city', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'billing_state', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'billing_country', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'billing_postal_code', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'total', 'type': 'FLOAT64', 'mode': 'NULLABLE'},
],
autodetect=False,
write_disposition='WRITE_TRUNCATE', #If the table already exists - overwrites the table data
)
start >> extracted_transformed_data
extracted_transformed_data >> stored_data_gcs
stored_data_gcs >> loaded_data_bigquery
loaded_data_bigquery >> end
def test_labels(self, bq_hook):
operator = GCSToBigQueryOperator(
task_id=TASK_ID,
bucket=TEST_BUCKET,
source_objects=TEST_SOURCE_OBJECTS,
destination_project_dataset_table=TEST_EXPLICIT_DEST,
labels=LABELS,
)
operator.execute(None)
bq_hook.return_value.get_conn.return_value.cursor.return_value.run_load.assert_called_once_with(
destination_project_dataset_table=mock.ANY,
schema_fields=mock.ANY,
source_uris=mock.ANY,
source_format=mock.ANY,
autodetect=mock.ANY,
create_disposition=mock.ANY,
skip_leading_rows=mock.ANY,
write_disposition=mock.ANY,
field_delimiter=mock.ANY,
max_bad_records=mock.ANY,
quote_character=mock.ANY,
ignore_unknown_values=mock.ANY,
allow_quoted_newlines=mock.ANY,
allow_jagged_rows=mock.ANY,
encoding=mock.ANY,
schema_update_options=mock.ANY,
src_fmt_configs=mock.ANY,
time_partitioning=mock.ANY,
cluster_fields=mock.ANY,
encryption_configuration=mock.ANY,
labels=LABELS,
description=mock.ANY,
)
def bs_file1000_dag():
@task()
def extract_transform():
df = pd.read_excel(f"{DATA_PATH}/file_1000.xls",
index_col=0).reset_index(drop=True)
df = df.drop(columns='First Name.1')
df['full_name'] = df['First Name'] + " " + df['Last Name']
df['gender'] = df['Gender'].apply(lambda row: 'M'
if row == 'Male' else 'F')
df['date'] = pd.to_datetime(df['Date'],
format='%d/%m/%Y',
errors='coerce')
df = df.drop(columns=['Date', 'Gender'])
df.columns = [
'first_name', 'last_name', 'country', 'age', 'id', 'full_name',
'gender', 'date'
]
df = df.reindex(columns=[
'id', 'first_name', 'last_name', 'full_name', 'date', 'age',
'gender', 'country'
]).reset_index(drop=True)
df.to_csv(OUT_PATH, index=False, header=False)
start = DummyOperator(task_id='start')
end = DummyOperator(task_id='end')
extract_task = extract_transform()
stored_data_gcs = LocalFilesystemToGCSOperator(
task_id="store_to_gcs",
gcp_conn_id=GOOGLE_CLOUD_CONN_ID,
src=OUT_PATH,
dst=GCS_OBJECT_NAME,
bucket=BUCKET_NAME)
loaded_data_bigquery = GCSToBigQueryOperator(
task_id='load_to_bigquery',
bigquery_conn_id=GOOGLE_CLOUD_CONN_ID,
bucket=BUCKET_NAME,
source_objects=[GCS_OBJECT_NAME],
destination_project_dataset_table=f"{DATASET_ID}.{BIGQUERY_TABLE_NAME}",
schema_fields=[ #based on https://cloud.google.com/bigquery/docs/schemas
{
'name': 'id',
'type': 'INT64',
'mode': 'REQUIRED'
},
{
'name': 'first_name',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'last_name',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'full_name',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'date',
'type': 'DATE',
'mode': 'NULLABLE'
},
{
'name': 'age',
'type': 'INT64',
'mode': 'NULLABLE'
},
{
'name': 'gender',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'country',
'type': 'STRING',
'mode': 'NULLABLE'
},
],
autodetect=False,
write_disposition=
'WRITE_TRUNCATE', #If the table already exists - overwrites the table data
)
start >> extract_task
extract_task >> stored_data_gcs
stored_data_gcs >> loaded_data_bigquery
loaded_data_bigquery >> end
# [START howto_operator_facebook_ads_to_gcs]
run_operator = FacebookAdsReportToGcsOperator(
task_id='run_fetch_data',
owner='airflow',
bucket_name=GCS_BUCKET,
parameters=PARAMETERS,
fields=FIELDS,
gcp_conn_id=GCS_CONN_ID,
object_name=GCS_OBJ_PATH,
)
# [END howto_operator_facebook_ads_to_gcs]
load_csv = GCSToBigQueryOperator(
task_id='gcs_to_bq_example',
bucket=GCS_BUCKET,
source_objects=[GCS_OBJ_PATH],
destination_project_dataset_table=f"{DATASET_NAME}.{TABLE_NAME}",
write_disposition='WRITE_TRUNCATE',
)
read_data_from_gcs_many_chunks = BigQueryInsertJobOperator(
task_id="read_data_from_gcs_many_chunks",
configuration={
"query": {
"query": f"SELECT COUNT(*) FROM `{GCP_PROJECT_ID}.{DATASET_NAME}.{TABLE_NAME}`",
"useLegacySql": False,
}
},
)
delete_bucket = GCSDeleteBucketOperator(
local_pq_to_gcs = LocalFilesystemToGCSOperator(
task_id='local_pq_to_gcs',
src=func_param['source_transform'],
dst=func_param['dest_blob_transform'],
bucket=func_param['bucket_name'],
gcp_conn_id='google_cloud_default',
trigger_rule='all_done',
dag=dag)
load_gcs_pq_to_bq = GCSToBigQueryOperator(
task_id='load_gcs_pq_to_bq',
bucket=func_param['bucket_name'],
source_objects=[func_param['dest_blob_transform']
], #default take list of objects,
destination_project_dataset_table='project_four_airflow.sales',
source_format='PARQUET',
write_disposition='WRITE_APPEND',
google_cloud_storage_conn_id='google_cloud_default',
trigger_rule='all_done',
dag=dag)
bigquery_is_up_to_date = DummyOperator(task_id='bigquery_is_up_to_date')
csv_file_not_exist = DummyOperator(task_id='csv_file_not_exist')
csv_file_exist = DummyOperator(task_id='csv_file_exist')
check_mysql = DummyOperator(task_id='check_mysql')
updating_dataset = DummyOperator(task_id='updating_dataset')
check_data >> check_mysql >> check_dataset
check_data >> csv_file_not_exist >> download_zip >> load_to_mysql >> check_dataset
check_data >> csv_file_exist >> load_to_mysql >> check_dataset
# [START howto_google_display_video_deletequery_report_operator]
delete_report = GoogleDisplayVideo360DeleteReportOperator(report_id=report_id, task_id="delete_report")
# [END howto_google_display_video_deletequery_report_operator]
create_report >> run_report >> wait_for_report >> get_report >> delete_report
with models.DAG(
"example_display_video_misc",
schedule_interval=None, # Override to match your needs,
start_date=dates.days_ago(1),
) as dag2:
# [START howto_google_display_video_upload_multiple_entity_read_files_to_big_query]
upload_erf_to_bq = GCSToBigQueryOperator(
task_id='upload_erf_to_bq',
bucket=BUCKET,
source_objects=ERF_SOURCE_OBJECT,
destination_project_dataset_table=f"{BQ_DATA_SET}.gcs_to_bq_table",
write_disposition='WRITE_TRUNCATE',
)
# [END howto_google_display_video_upload_multiple_entity_read_files_to_big_query]
# [START howto_google_display_video_download_line_items_operator]
download_line_items = GoogleDisplayVideo360DownloadLineItemsOperator(
task_id="download_line_items",
request_body=DOWNLOAD_LINE_ITEMS_REQUEST,
bucket_name=BUCKET,
object_name=OBJECT_NAME,
gzip=False,
)
# [END howto_google_display_video_download_line_items_operator]
def bs_disaster_dag():
@task()
def extract_transform():
df = pd.read_csv(f"{DATA_PATH}/disaster_data.csv")
columns = ['text', 'location']
for column in columns:
df[column] = df[column].str.replace(r'\s{2,}', ' ', regex=True)
df[column] = df[column].str.replace(r"[^a-zA-Z0-9\,]",
' ',
regex=True)
df.to_csv(OUT_PATH, index=False, header=False)
start = DummyOperator(task_id='start')
end = DummyOperator(task_id='end')
extract_transform_task = extract_transform()
stored_data_gcs = LocalFilesystemToGCSOperator(
task_id="store_to_gcs",
gcp_conn_id=GOOGLE_CLOUD_CONN_ID,
src=OUT_PATH,
dst=GCS_OBJECT_NAME,
bucket=BUCKET_NAME)
loaded_data_bigquery = GCSToBigQueryOperator(
task_id='load_to_bigquery',
bigquery_conn_id=GOOGLE_CLOUD_CONN_ID,
bucket=BUCKET_NAME,
source_objects=[GCS_OBJECT_NAME],
destination_project_dataset_table=f"{DATASET_ID}.{BIGQUERY_TABLE_NAME}",
schema_fields=[ #based on https://cloud.google.com/bigquery/docs/schemas
{
'name': 'id',
'type': 'INT64',
'mode': 'REQUIRED'
},
{
'name': 'keyword',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'location',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'text',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'target',
'type': 'INT64',
'mode': 'NULLABLE'
},
],
autodetect=False,
write_disposition=
'WRITE_TRUNCATE', #If the table already exists - overwrites the table data
)
start >> extract_transform_task
extract_transform_task >> stored_data_gcs
stored_data_gcs >> loaded_data_bigquery
loaded_data_bigquery >> end
def bs_reviews_dag():
@task()
def merge_reviews(reviews: list):
df_merge = pd.concat([pd.read_json(review) for review in reviews],
ignore_index=True)
print(df_merge)
df_merge.to_csv(OUT_PATH, index=False, header=False)
@task()
def extract_reviews(filename):
print(filename)
file_path = f"{DATA_PATH}/{filename}"
if 'csv' in filename:
df = pd.read_csv(file_path)
else:
df = pd.read_excel(file_path)
print(df)
return df.to_json()
start = DummyOperator(task_id='start')
end = DummyOperator(task_id='end')
filenames = os.listdir(DATA_PATH)
filtered_filename = list(
filter(lambda filename: re.match(r"(^reviews)", filename), filenames))
extracted_list = []
for i in range(len(filtered_filename)):
extracted = extract_reviews(filtered_filename[i])
extracted_list.append(extracted)
merged = merge_reviews(extracted_list)
stored_data_gcs = LocalFilesystemToGCSOperator(
task_id="store_to_gcs",
gcp_conn_id=GOOGLE_CLOUD_CONN_ID,
src=OUT_PATH,
dst=GCS_OBJECT_NAME,
bucket=BUCKET_NAME)
loaded_data_bigquery = GCSToBigQueryOperator(
task_id='load_to_bigquery',
bigquery_conn_id=GOOGLE_CLOUD_CONN_ID,
bucket=BUCKET_NAME,
source_objects=[GCS_OBJECT_NAME],
destination_project_dataset_table=f"{DATASET_ID}.{BIGQUERY_TABLE_NAME}",
schema_fields=[ #based on https://cloud.google.com/bigquery/docs/schemas
{
'name': 'listing_id',
'type': 'INT64',
'mode': 'NULLABLE'
},
{
'name': 'id',
'type': 'INT64',
'mode': 'REQUIRED'
},
{
'name': 'date',
'type': 'DATE',
'mode': 'NULLABLE'
},
{
'name': 'reviewer_id',
'type': 'INT64',
'mode': 'NULLABLE'
},
{
'name': 'reviewer_name',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'comments',
'type': 'STRING',
'mode': 'NULLABLE'
},
],
autodetect=False,
allow_quoted_newlines=True,
write_disposition=
'WRITE_TRUNCATE', #If the table already exists - overwrites the table data
)
start >> extracted_list >> merged
merged >> stored_data_gcs
stored_data_gcs >> loaded_data_bigquery >> end
load_users = GCSToBigQueryOperator(
task_id='gcs_to_bigquery_users',
bucket='airflow-postgres',
source_objects=['users.csv'],
destination_project_dataset_table=f"{DATASET_NAME}.{TABLE_NAME}",
schema_fields=[
{
'name': 'created_at',
'type': 'TIMESTAMP',
'mode': 'REQUIRED'
},
{
'name': 'first_name',
'type': 'STRING',
'mode': 'REQUIRED'
},
{
'name': 'id',
'type': 'STRING',
'mode': 'REQUIRED'
},
{
'name': 'last_name',
'type': 'STRING',
'mode': 'REQUIRED'
},
{
'name': 'updated_at',
'type': 'TIMESTAMP',
'mode': 'REQUIRED'
},
],
write_disposition='WRITE_TRUNCATE',
source_format='CSV',
encoding='UTF-8',
dag=dag,
)
def bs_tweets_dag():
@task()
def extract_transform_tweets():
df = pd.read_json(f"{DATA_PATH}/tweet_data.json", lines=True)
df['created_at'] = df['created_at'].dt.tz_convert(None)
columns = ['text', 'source']
for column in columns:
df[column] = df[column].str.replace(r"[^a-zA-Z0-9\,#@]", ' ', regex=True)
df[column] = df[column].str.replace(r"\s{2,}", ' ', regex=True)
filtered_columns = filter(lambda col:
col != 'extended_entities' and
col != 'contributors' and col != 'entities'
and col != 'retweeted_status' and col != 'user'
and col != 'in_reply_to_user_id_str'
and col != 'in_reply_to_status_id_str' ,
list(df.columns))
df_filtered = df[filtered_columns]
df_filtered.to_csv(OUT_TWEETS_PATH, index=False, header=False)
@task()
def extract_transform_tweets_user():
df = pd.read_json(f"{DATA_PATH}/tweet_data.json", lines=True)
users = [ {**row['user'], 'tweet_id': row['id']} for _, row in df.iterrows() ]
df_users = pd.DataFrame(users)
df_users['created_at'] = pd.to_datetime(df_users['created_at'],
format='%a %b %d %H:%M:%S %z %Y'
).dt.tz_convert(None)
filtered_column = list(filter(lambda col:
col != 'id' and col != 'tweet_id',
list(df_users.columns)))
df_users = df_users.reindex(columns=['id', 'tweet_id', *(filtered_column)])
df_users.to_csv(OUT_TWEETS_USER_PATH, index=False, header=False)
start = DummyOperator(task_id='start')
end = DummyOperator(task_id='end')
et_tweets = extract_transform_tweets()
et_tweets_user = extract_transform_tweets_user()
stored_tweets_data_gcs = LocalFilesystemToGCSOperator(
task_id="store_tweets_to_gcs",
gcp_conn_id=GOOGLE_CLOUD_CONN_ID,
src=OUT_TWEETS_PATH,
dst=GCS_OBJECT_TWEETS_NAME,
bucket=BUCKET_NAME
)
stored_tweets_user_data_gcs = LocalFilesystemToGCSOperator(
task_id="store_tweets_user_to_gcs",
gcp_conn_id=GOOGLE_CLOUD_CONN_ID,
src=OUT_TWEETS_USER_PATH,
dst=GCS_OBJECT_TWEETS_USER_NAME,
bucket=BUCKET_NAME
)
loaded_tweets_data_bigquery = GCSToBigQueryOperator(
task_id='load_tweets_to_bigquery',
bigquery_conn_id=GOOGLE_CLOUD_CONN_ID,
bucket=BUCKET_NAME,
source_objects=[GCS_OBJECT_TWEETS_NAME],
destination_project_dataset_table=f"{DATASET_ID}.{BIGQUERY_TWEETS_TABLE_NAME}",
schema_fields=[ #based on https://cloud.google.com/bigquery/docs/schemas
{'name': 'truncated', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'text', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'is_quote_status', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'in_reply_to_status_id', 'type': 'FLOAT64', 'mode': 'NULLABLE'},
{'name': 'in_reply_to_user_id', 'type': 'FLOAT64', 'mode': 'NULLABLE'},
{'name': 'id', 'type': 'INT64', 'mode': 'REQUIRED'},
{'name': 'favorite_count', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'retweeted', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'coordinates', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'source', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'in_reply_to_screen_name', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'id_str', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'retweet_count', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'metadata', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'favorited', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'geo', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'lang', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'created_at', 'type': 'DATETIME', 'mode': 'NULLABLE'},
{'name': 'place', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'quoted_status_id', 'type': 'FLOAT64', 'mode': 'NULLABLE'},
{'name': 'quoted_status', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'possibly_sensitive', 'type': 'FLOAT64', 'mode': 'NULLABLE'},
{'name': 'quoted_status_id_str', 'type': 'FLOAT64', 'mode': 'NULLABLE'},
],
autodetect=False,
write_disposition='WRITE_TRUNCATE', #If the table already exists - overwrites the table data
)
loaded_tweets_user_data_bigquery = GCSToBigQueryOperator(
task_id='load_tweets_user_to_bigquery',
bigquery_conn_id=GOOGLE_CLOUD_CONN_ID,
bucket=BUCKET_NAME,
source_objects=[GCS_OBJECT_TWEETS_USER_NAME],
destination_project_dataset_table=f"{DATASET_ID}.{BIGQUERY_TWEETS_USER_TABLE_NAME}",
schema_fields=[
{'name': 'id', 'type': 'INT64', 'mode': 'REQUIRED'},
{'name': 'tweet_id', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'follow_request_sent', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'has_extended_profile', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'profile_use_background_image', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'verified', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'translator_type', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_text_color', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_image_url_https', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_sidebar_fill_color', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'entities', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'followers_count', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'protected', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'location', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'default_profile_image', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'id_str', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'lang', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'utc_offset', 'type': 'FLOAT64', 'mode': 'NULLABLE'},
{'name': 'statuses_count', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'description', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'friends_count', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'profile_background_image_url_https', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_link_color', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_image_url', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'following', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'geo_enabled', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'profile_background_color', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_banner_url', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_background_image_url', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'screen_name', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'is_translation_enabled', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'profile_background_tile', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'favourites_count', 'type': 'INT64', 'mode': 'NULLABLE'},
{'name': 'name', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'notifications', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'url', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'created_at', 'type': 'DATETIME', 'mode': 'NULLABLE'},
{'name': 'contributors_enabled', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'time_zone', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'profile_sidebar_border_color', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'default_profile', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'is_translator', 'type': 'BOOL', 'mode': 'NULLABLE'},
{'name': 'listed_count', 'type': 'INT64', 'mode': 'NULLABLE'},
],
autodetect=False,
allow_quoted_newlines=True,
write_disposition='WRITE_TRUNCATE',
)
start >> [et_tweets, et_tweets_user]
et_tweets >> stored_tweets_data_gcs
et_tweets_user >> stored_tweets_user_data_gcs
stored_tweets_data_gcs >> loaded_tweets_data_bigquery >> end
stored_tweets_user_data_gcs >> loaded_tweets_user_data_bigquery >> end
def bs_database_sqlite_dag():
@task()
def extract_transform():
conn = sqlite3.connect(f"{DATA_PATH}/database.sqlite")
with open(f"{BASE_PATH}/sql/database_sqlite.sql", "r") as query:
df = pd.read_sql(query.read(), conn)
df.to_csv(OUT_PATH, index=False, header=False
) #prevent on create Index column and exclude the header row
start = DummyOperator(task_id='start')
end = DummyOperator(task_id='end')
extracted_transformed_data = extract_transform()
stored_data_gcs = LocalFilesystemToGCSOperator(
task_id="store_to_gcs",
gcp_conn_id=GOOGLE_CLOUD_CONN_ID,
src=OUT_PATH,
dst='extract_transform_database_sqlite.csv',
bucket=BUCKET_NAME)
loaded_data_bigquery = GCSToBigQueryOperator(
task_id='load_to_bigquery',
bigquery_conn_id=GOOGLE_CLOUD_CONN_ID,
bucket=BUCKET_NAME,
source_objects=[GCS_OBJECT_NAME],
destination_project_dataset_table=f"{DATASET_ID}.{BIGQUERY_TABLE_NAME}",
schema_fields=[ #based on https://cloud.google.com/bigquery/docs/schemas
{
'name': 'reviewid',
'type': 'INT64',
'mode': 'REQUIRED'
},
{
'name': 'title',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'artist',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'url',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'score',
'type': 'FLOAT64',
'mode': 'NULLABLE'
},
{
'name': 'best_new_music',
'type': 'INTEGER',
'mode': 'NULLABLE'
},
{
'name': 'author',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'author_type',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'pub_date',
'type': 'DATE',
'mode': 'NULLABLE'
},
{
'name': 'pub_weekday',
'type': 'INT64',
'mode': 'NULLABLE'
},
{
'name': 'pub_day',
'type': 'INT64',
'mode': 'NULLABLE'
},
{
'name': 'pub_month',
'type': 'INT64',
'mode': 'NULLABLE'
},
{
'name': 'pub_year',
'type': 'INT64',
'mode': 'NULLABLE'
},
{
'name': 'concat_genre',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'concat_label',
'type': 'STRING',
'mode': 'NULLABLE'
},
{
'name': 'concat_year',
'type': 'STRING',
'mode': 'NULLABLE'
},
],
autodetect=False,
write_disposition=
'WRITE_TRUNCATE', #If the table already exists - overwrites the table data
)
start >> extracted_transformed_data
extracted_transformed_data >> stored_data_gcs
stored_data_gcs >> loaded_data_bigquery
loaded_data_bigquery >> end
start = DummyOperator(task_id='start')
conn_task = PythonOperator(
task_id='conn-task',
python_callable=get_conn,
op_kwargs={'my_conn_id': 'smtp_default'},
)
var_task = PythonOperator(task_id='var-task',
python_callable=get_var,
op_kwargs={'var_name': 'hello'})
bq_load_task = GCSToBigQueryOperator(
task_id='bqload',
bucket=Variable.get('bucket_secret'),
source_objects=['astro_word_count.csv'],
destination_project_dataset_table=Variable.get('bqtable_secret'),
skip_leading_rows=1,
schema_fields=[
{
'name': 'word',
'type': 'STRING'
},
{
'name': 'count',
'type': 'INT64'
},
],
write_disposition='WRITE_TRUNCATE')
start >> [conn_task, var_task, bq_load_task]
import_in_bigquery = GCSToBigQueryOperator(
task_id="import_in_bigquery",
bucket=os.environ["RATINGS_BUCKET"],
source_objects=[
"ratings/{{ execution_date.year }}/{{ execution_date.strftime('%m') }}.csv"
],
source_format="CSV",
create_disposition="CREATE_IF_NEEDED",
write_disposition="WRITE_TRUNCATE",
bigquery_conn_id="gcp",
skip_leading_rows=1,
schema_fields=[
{
"name": "userId",
"type": "INTEGER"
},
{
"name": "movieId",
"type": "INTEGER"
},
{
"name": "rating",
"type": "FLOAT"
},
{
"name": "timestamp",
"type": "TIMESTAMP"
},
],
destination_project_dataset_table=(os.environ["GCP_PROJECT"] + ":" +
os.environ["BIGQUERY_DATASET"] + "." +
"ratings${{ ds_nodash }}"),
dag=dag,
)
loadGcsToBq = GCSToBigQueryOperator(
task_id='gcstobq_jagged',
bucket=SOURCE_BUCKET,
source_objects=[SRC1],
destination_project_dataset_table=f"{DATASET_NAME}.{TABLE_NAME}",
create_disposition='CREATE_IF_NEEDED',
source_format='CSV',
write_disposition='WRITE_TRUNCATE',
allow_jagged_rows=True,
skip_leading_rows=1,
schema_fields=[
{
"mode": "NULLABLE",
"name": "year",
"type": "INTEGER"
},
{
"mode": "NULLABLE",
"name": "score",
"type": "INTEGER"
},
{
"mode": "NULLABLE",
"name": "title",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "tbd1",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "tbd2",
"type": "STRING"
},
]
)
task_id='create_stage_reviews_table',
dataset_id=GCP_BQ_DATASET_STAGE,
table_id=GCP_BQ_TABLE_REVIEWS,
project_id=GCP_PROJECT_ID,
bigquery_conn_id='gr_bigquery_conn',
schema_fields=stage.reviews_schema)
load_stage_data = DummyOperator(task_id='load_stage_data')
load_stage_users_data = GCSToBigQueryOperator(
task_id='load_stage_users_data',
bucket=os.environ['GCP_GCS_BUCKET_PROCESSED'],
source_objects=['{{ run_id }}/user/part-*'],
#source_objects=['test/user/part-*'],
destination_project_dataset_table=
f"{os.environ['GCP_PROJECT_ID']}.{os.environ['GCP_BQ_DATASET_STAGE']}.{os.environ['GCP_BQ_TABLE_USERS']}",
schema_fields=users_schema,
source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,
create_disposition=bigquery.CreateDisposition.CREATE_NEVER,
write_disposition=bigquery.WriteDisposition.WRITE_EMPTY,
skip_leading_rows=1,
bigquery_conn_id='gr_bigquery_conn',
google_cloud_storage_conn_id='gr_storage_conn')
load_stage_authors_data = GCSToBigQueryOperator(
task_id='load_stage_authors_data',
bucket=os.environ['GCP_GCS_BUCKET_PROCESSED'],
source_objects=['{{ run_id }}/author/part-*'],
# source_objects=['test/author/part-*'],
destination_project_dataset_table=
f"{os.environ['GCP_PROJECT_ID']}.{os.environ['GCP_BQ_DATASET_STAGE']}.{os.environ['GCP_BQ_TABLE_AUTHORS']}",
schema_fields=authors_schema,
start_date=datetime(2021, 1, 1),
catchup=False,
schedule_interval='@once',
tags=['example'],
) as dag:
create_test_dataset = BigQueryCreateEmptyDatasetOperator(
task_id='create_airflow_test_dataset', dataset_id=DATASET_NAME
)
# [START howto_operator_gcs_to_bigquery]
load_csv = GCSToBigQueryOperator(
task_id='gcs_to_bigquery_example',
bucket='cloud-samples-data',
source_objects=['bigquery/us-states/us-states.csv'],
destination_project_dataset_table=f"{DATASET_NAME}.{TABLE_NAME}",
schema_fields=[
{'name': 'name', 'type': 'STRING', 'mode': 'NULLABLE'},
{'name': 'post_abbr', 'type': 'STRING', 'mode': 'NULLABLE'},
],
write_disposition='WRITE_TRUNCATE',
)
# [END howto_operator_gcs_to_bigquery]
delete_test_dataset = BigQueryDeleteDatasetOperator(
task_id='delete_airflow_test_dataset',
dataset_id=DATASET_NAME,
delete_contents=True,
)
create_test_dataset >> load_csv >> delete_test_dataset
"query": {
"query": "{% include 'create_bad_row_table.sql' %}",
"use_legacy_sql": False,
}
},
dag=dag,
)
load_data_from_gsc_to_bigquery = GCSToBigQueryOperator(
task_id="load_data_from_gsc_to_bigquery",
bucket="{{ var.json.env.bucket }}",
source_objects=[
'data/{{execution_date.strftime("%Y") }}/{{execution_date.strftime("%m") }}/{{execution_date.strftime("%d") }}*'
],
destination_project_dataset_table=f"{stg_dataset_name}.{stg_table_name}",
skip_leading_rows=1,
source_format="CSV",
create_disposition="CREATE_IF_NEEDED",
write_disposition="WRITE_APPEND",
schema_update_options=["ALLOW_FIELD_RELAXATION", "ALLOW_FIELD_ADDITION"],
autodetect=True,
dag=dag,
)
quality_check = DataQualityOperator(
task_id="quality_check",
provide_context=True,
gcp_conn_id="google_cloud_default",
sql='{% include "quality_check.sql" %}',
table_list=[
"{{ var.json.env.project }}.{{ var.json.env.stg }}.{{ var.json.env.raw_data }}"