Untitled
unknown
plain_text
8 days ago
12 kB
2
Indexable
Never
# Define table name and create the Sports table table_name = "slvr_crtd_nrt_back.tab_info_service_sports" # Replace 'your_database' with your actual database name spark.sql(f""" CREATE TABLE IF NOT EXISTS {table_name} ( tab_info_service_sports_pk STRING, -- New Primary Key Column id STRING NOT NULL, name STRING, displayName STRING, spectrumId STRING, sameGame BOOLEAN, self_link STRING, selfTemplate_link STRING, competitions_link STRING, footytab_link STRING, tournaments_link STRING, matches_link STRING, markets_link STRING, markets_all_channels_link STRING, created_datetime TIMESTAMP, -- Metadata Column updated_datetime TIMESTAMP, -- Metadata Column deleted_datetime TIMESTAMP, -- Metadata Column change_status STRING -- Metadata Column ) USING DELTA CLUSTER BY (id); """) # Add comments to each column in the Sports table spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_sports_pk COMMENT 'Primary key for the Sports table';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN id COMMENT 'Unique identifier for the sport';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN name COMMENT 'Name of the sport';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN displayName COMMENT 'Display name of the sport';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN spectrumId COMMENT 'Spectrum identifier';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sameGame COMMENT 'Indicator if same game is available';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN self_link COMMENT 'Self link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN selfTemplate_link COMMENT 'Self template link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competitions_link COMMENT 'Competitions link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN footytab_link COMMENT 'Footytab link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tournaments_link COMMENT 'Tournaments link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN matches_link COMMENT 'Matches link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_link COMMENT 'Markets link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_all_channels_link COMMENT 'Markets all channels link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""") # List of all column names for the Sports table including new metadata columns columns = [ "tab_info_service_sports_pk", "id", "name", "displayName", "spectrumId", "sameGame", "self_link", "selfTemplate_link", "competitions_link", "footytab_link", "tournaments_link", "matches_link", "markets_link", "markets_all_channels_link", "created_datetime", "updated_datetime", "deleted_datetime", "change_status" ] # Set 'Internal' classification for all columns in the Sports table for col_name in columns: spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""") # Update table properties for Delta Lake features in the Sports table spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES ( delta.enableChangeDataFeed = true, delta.logRetentionDuration = 'interval 90 days', delta.deletedFileRetentionDuration = 'interval 90 days' );""") # Define table name and create the Competitions table table_name = "slvr_crtd_nrt_back.tab_info_service_competitions" spark.sql(f""" CREATE TABLE IF NOT EXISTS {table_name} ( tab_info_service_sports_pk STRING, -- New Primary Key Column sport_id STRING NOT NULL, id STRING NOT NULL, name STRING, spectrumId STRING, hasMarkets BOOLEAN, sameGame BOOLEAN, self_link STRING, selfTemplate_link STRING, competitions_link STRING, footytab_link STRING, tournaments_link STRING, matches_link STRING, markets_link STRING, markets_all_channels_link STRING, created_datetime TIMESTAMP, -- Metadata Column updated_datetime TIMESTAMP, -- Metadata Column deleted_datetime TIMESTAMP, -- Metadata Column change_status STRING -- Metadata Column ) USING DELTA CLUSTER BY (sport_id, id); """) # Add comments to each column in the Competitions table spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_sports_pk COMMENT 'Primary key for the Competitions table';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sport_id COMMENT 'Foreign key referencing sports.id';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN id COMMENT 'Unique identifier for the competition';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN name COMMENT 'Name of the competition';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN spectrumId COMMENT 'Spectrum identifier';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN hasMarkets COMMENT 'Indicator if markets are available';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sameGame COMMENT 'Indicator if same game is available';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN self_link COMMENT 'Self link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN selfTemplate_link COMMENT 'Self template link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competitions_link COMMENT 'Competitions link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN footytab_link COMMENT 'Footytab link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tournaments_link COMMENT 'Tournaments link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN matches_link COMMENT 'Matches link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_link COMMENT 'Markets link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_all_channels_link COMMENT 'Markets all channels link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""") # List of all column names for the Competitions table including new metadata columns columns = [ "tab_info_service_sports_pk", "sport_id", "id", "name", "spectrumId", "hasMarkets", "sameGame", "self_link", "selfTemplate_link", "competitions_link", "footytab_link", "tournaments_link", "matches_link", "markets_link", "markets_all_channels_link", "created_datetime", "updated_datetime", "deleted_datetime", "change_status" ] # Set 'Internal' classification for all columns in the Competitions table for col_name in columns: spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""") # Update table properties for Delta Lake features in the Competitions table spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES ( delta.enableChangeDataFeed = true, delta.logRetentionDuration = 'interval 90 days', delta.deletedFileRetentionDuration = 'interval 90 days' );""") # Define table name and create the Tournaments table table_name = "slvr_crtd_nrt_back.tab_info_service_tournaments" spark.sql(f""" CREATE TABLE IF NOT EXISTS {table_name} ( tab_info_service_sports_pk STRING, -- New Primary Key Column sport_id STRING NOT NULL, competition_id STRING NOT NULL, id STRING NOT NULL, name STRING, spectrumId STRING, sameGame BOOLEAN, self_link STRING, selfTemplate_link STRING, competitions_link STRING, footytab_link STRING, tournaments_link STRING, matches_link STRING, markets_link STRING, markets_all_channels_link STRING, created_datetime TIMESTAMP, -- Metadata Column updated_datetime TIMESTAMP, -- Metadata Column deleted_datetime TIMESTAMP, -- Metadata Column change_status STRING -- Metadata Column ) USING DELTA CLUSTER BY (sport_id, competition_id, id); """) # Add comments to each column in the Tournaments table spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tab_info_service_sports_pk COMMENT 'Primary key for the Tournaments table';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sport_id COMMENT 'Foreign key referencing sports.id';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competition_id COMMENT 'Foreign key referencing competitions.id';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN id COMMENT 'Unique identifier for the tournament';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN name COMMENT 'Name of the tournament';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN spectrumId COMMENT 'Spectrum identifier';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN sameGame COMMENT 'Indicator if same game is available';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN self_link COMMENT 'Self link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN selfTemplate_link COMMENT 'Self template link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN competitions_link COMMENT 'Competitions link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN footytab_link COMMENT 'Footytab link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN tournaments_link COMMENT 'Tournaments link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN matches_link COMMENT 'Matches link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_link COMMENT 'Markets link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN markets_all_channels_link COMMENT 'Markets all channels link';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN created_datetime COMMENT 'Timestamp when the record was created';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN updated_datetime COMMENT 'Timestamp when the record was last updated';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN deleted_datetime COMMENT 'Timestamp when the record was deleted';""") spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN change_status COMMENT 'Status of the change';""") # List of all column names for the Tournaments table including new metadata columns columns = [ "tab_info_service_sports_pk", "sport_id", "competition_id", "id", "name", "spectrumId", "sameGame", "self_link", "selfTemplate_link", "competitions_link", "footytab_link", "tournaments_link", "matches_link", "markets_link", "markets_all_channels_link", "created_datetime", "updated_datetime", "deleted_datetime", "change_status" ] # Set 'Internal' classification for all columns in the Tournaments table for col_name in columns: spark.sql(f"""ALTER TABLE {table_name} ALTER COLUMN {col_name} SET TAGS ('Classification' = 'Internal');""") # Update table properties for Delta Lake features in the Tournaments table spark.sql(f"""ALTER TABLE {table_name} SET TBLPROPERTIES ( delta.enableChangeDataFeed = true, delta.logRetentionDuration = 'interval 90 days', delta.deletedFileRetentionDuration = 'interval 90 days' );""")
Leave a Comment