CREATE EXTERNAL TABLES

The following bq command line will create external table for streams, tracks, and users. Ensure the schema files are in the same location. Only need to create the external table once. 

bq mk --external_table_definition=./external_streams_schema.json@NEWLINE_DELIMITED_JSON=gs://sme-dsp-raw-demo/spotify/incoming/spotify_streams* spotify.external_streams
bq mk --external_table_definition=./external_tracks_schema.json@NEWLINE_DELIMITED_JSON=gs://sme-dsp-raw-demo/spotify/incoming/spotify_tracks* spotify.external_tracks
bq mk --external_table_definition=./external_users_schema.json@NEWLINE_DELIMITED_JSON=gs://sme-dsp-raw-demo/spotify/incoming/spotify_users* spotify.external_users


CREATE INTERNAL TABLES
The following queries create internal partitioned tables for streams, tracks, and users. 

CREATE TABLE spotify.spotify_streams
PARTITION BY report_date
AS

SELECT * EXCEPT(timestamp, offline_timestamp, filename,country),
 SAFE.PARSE_DATETIME('%Y%m%dT%H:%M:%S', timestamp) AS timestamp,
 SAFE.PARSE_DATETIME('%Y%m%dT%H:%M:%S',offline_timestamp ) AS offline_timestamp,
 CAST(REGEXP_EXTRACT(filename, r'_([0-9]*-[0-9]*-[0-9]*)_') AS date) AS report_date,
 REGEXP_EXTRACT(filename, r'v[0-9]_([a-z]*)_') AS report_licensor,
 REGEXP_EXTRACT(filename, r'_([a-z]*)\.') AS report_country

FROM (
 SELECT
   *,
   _FILE_NAME AS filename
 FROM
   spotify.external_streams
 )


CREATE TABLE spotify.spotify_tracks
PARTITION BY report_date
AS

SELECT * EXCEPT(filename),
 CAST(REGEXP_EXTRACT(filename, r'_([0-9]*-[0-9]*-[0-9]*)\.') AS date) AS report_date,
 REGEXP_EXTRACT(filename, r'v[0-9]_([a-z]*)_') AS report_licensor
FROM (
 SELECT
   *,
   _FILE_NAME AS filename
 FROM
   spotify.external_tracks
 )


CREATE TABLE spotify.spotify_users
PARTITION BY report_date
AS

SELECT * EXCEPT(filename),
 CAST(REGEXP_EXTRACT(filename, r'_([0-9]*-[0-9]*-[0-9]*)\.') AS date) AS report_date,
 REGEXP_EXTRACT(filename, r'v[0-9]_([a-z]*)_') AS report_licensor
FROM (
 SELECT
   *,
   _FILE_NAME AS filename
 FROM
   spotify.external_users
 )


DELETE DATA

The following command line will remove all the data in a table without deleting the partitioned table.
bq query --use_legacy_sql=false 'DELETE `spotify.aggregation` WHERE TRUE'
bq query --use_legacy_sql=false 'DELETE `spotify.spotify_streams` WHERE TRUE'
bq query --use_legacy_sql=false 'DELETE `spotify.spotify_tracks` WHERE TRUE'
bq query --use_legacy_sql=false 'DELETE `spotify.spotify_users` WHERE TRUE'