feat: Add geom columns for thelook_ecommerce dataset (#307)

GoogleCloudPlatform · Aug 25, 2022 · f39a177 · f39a177
1 parent ebfe4de
commit f39a177
Showing 1 changed file with 27 additions and 1 deletion.
diff --git a/datasets/thelook_ecommerce/pipelines/thelook_ecommerce/pipeline.yaml b/datasets/thelook_ecommerce/pipelines/thelook_ecommerce/pipeline.yaml
@@ -498,5 +498,31 @@ dag:
  type: "FLOAT"
  mode: "NULLABLE"
 
+ - operator: "BigQueryInsertJobOperator"
+ description: "Task to create the user geom column from the latitude and longitude columns"
+
+ args:
+ task_id: "create_user_geom_column"
+
+ # Query that creates the column as a GEOGRAPHY type, then populates it with a geographic point based on the longitude and latitude values
+ query: |-
+ ALTER TABLE `bigquery-public-data.thelook_ecommerce.users` ADD COLUMN IF NOT EXISTS user_geom GEOGRAPHY;
+ UPDATE `bigquery-public-data.thelook_ecommerce.users` SET user_geom = SAFE.ST_GeogFromText(CONCAT('POINT(',CAST(longitude AS STRING), ' ', CAST(latitude as STRING), ')')) WHERE longitude IS NOT NULL AND latitude IS NOT NULL;
+ # Use Legacy SQL should be false for any query that uses a DML statement
+ useLegacySql: False
+
+ - operator: "BigQueryInsertJobOperator"
+ description: "Task to create the distribution center geom column from the latitude and longitude columns"
+
+ args:
+ task_id: "create_distribution_center_geom_column"
+
+ # Query that creates the column as a GEOGRAPHY type, then populates it with a geographic point based on the longitude and latitude values
+ query: |-
+ ALTER TABLE `bigquery-public-data.thelook_ecommerce.distribution_centers` ADD COLUMN IF NOT EXISTS distribution_center_geom GEOGRAPHY;
+ UPDATE `bigquery-public-data.thelook_ecommerce.distribution_centers` SET distribution_center_geom = SAFE.ST_GeogFromText(CONCAT('POINT(',CAST(longitude AS STRING), ' ', CAST(latitude as STRING), ')')) WHERE longitude IS NOT NULL AND latitude IS NOT NULL;
+ # Use Legacy SQL should be false for any query that uses a DML statement
+ useLegacySql: False
+
  graph_paths:
- - "generate_thelook >> [load_products_to_bq, load_events_to_bq, load_inventory_items_to_bq, load_order_items_to_bq, load_orders_to_bq, load_users_to_bq, load_distribution_centers_to_bq]"
+ - "generate_thelook >> [load_products_to_bq, load_events_to_bq, load_inventory_items_to_bq, load_order_items_to_bq, load_orders_to_bq, load_users_to_bq, load_distribution_centers_to_bq] >> [create_user_geom_column, create_distribution_center_geom_column]"