Create Trino Tables

Table: segmentationmetadata

Create the segmentationmetadata table in Hive, and then create a view over it.

CREATE TABLE hive.default.segmentationmetadata_<tenantId> (
   advid varchar,
   datasourcename varchar,
   dimension varchar,
   datatype varchar,
   dbtype varchar,
   dbname varchar,
   tablecollectionname varchar,
   isenumerable varchar,
   primaryidentifier varchar,
   display_dimensions varchar,
   display_datasourcename varchar,
   cteventidentifier varchar,
   propertiesidentifier varchar,
   isrts varchar,
   cteventdateidentifier varchar,
   is_pii varchar,
   macro_name varchar
)
WITH (
   external_location = 's3a://<bucket-name>/Segmentation/segmentdb_<tenantId>/segmentationmetadata',
   format = 'CSV',
   skip_header_line_count = 1
)

CREATE VIEW minio.segmentdb_<tenantId>.segmentationmetadata SECURITY DEFINER AS
SELECT
  advid,
  datasourcename,
  dimension,
  datatype,
  dbtype,
  dbname,
  tablecollectionname,
  (CASE WHEN LOWER(TRIM(BOTH FROM isenumerable)) IN ('true', '1', 'yes') THEN true ELSE false END) AS isenumerable,
  (CASE WHEN LOWER(TRIM(BOTH FROM primaryidentifier)) IN ('true', '1', 'yes') THEN true ELSE false END) AS primaryidentifier,
  display_dimensions,
  display_datasourcename,
  (CASE WHEN LOWER(TRIM(BOTH FROM cteventidentifier)) IN ('true', '1', 'yes') THEN true ELSE false END) AS cteventidentifier,
  (CASE WHEN LOWER(TRIM(BOTH FROM propertiesidentifier)) IN ('true', '1', 'yes') THEN true ELSE false END) AS propertiesidentifier,
  isrts,
  (CASE WHEN LOWER(TRIM(BOTH FROM cteventdateidentifier)) IN ('true', '1', 'yes') THEN true ELSE false END) AS cteventdateidentifier,
  (CASE WHEN LOWER(TRIM(BOTH FROM is_pii)) IN ('true', '1', 'yes') THEN true ELSE false END) AS is_pii,
  macro_name
FROM hive.default.segmentationmetadata_<tenantId>;

Table: ctevent_property_metadata

Create the ctevent_property_metadata table in Hive, and then create a view over it.

CREATE TABLE hive.default.ctevent_property_metadata (
   eventname varchar,
   propertyname varchar,
   isenumerable varchar,
   covactivity varchar,
   propertydatatype varchar
)
WITH (
   external_location = 's3a://<bucket-name>/Segmentation/segmentdb_<tenantId>/ctevents_prop_metadata',
   format = 'CSV',
   skip_header_line_count = 1
)

CREATE VIEW minio.segmentdb_<tenantId>.ctevent_property_metadata SECURITY DEFINER AS
SELECT *
FROM
  hive.default.ctevent_property_metadata

Table: dimensionvalues

CREATE TABLE minio.segmentdb_<tenantId>.dimensionvalues (
   tenant varchar,
   tablename varchar,
   column_name varchar,
   distinctvalues varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2,
   location = 's3a://<bucket-name>/dimensionvalues/v1/<tenantId>',
   max_commit_retry = 4
)

Table: id_mapping

CREATE TABLE minio.segmentdb_<tenantId>.id_mapping (
   latest_id varchar,
   old_id varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2,
   location = 's3a://<bucket-name>/<tenantId>/id_mapping',
   max_commit_retry = 4
)

Table: master_events

CREATE TABLE minio.segmentdb_<tenantId>.master_events (
   properties map(varchar, varchar),
   eventname varchar,
   eventdate timestamp(6),
   eventtime varchar,
   key varchar,
   type varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2,
   location = 's3a://<bucket-name>/<tenantId>/master_events',
   max_commit_retry = 4,
   partitioning = ARRAY['day(eventdate)','eventname']
)

Table: profile_data_sync_athena

CREATE TABLE minio.segmentdb_<tenantId>.profile_data_sync_athena (
   customerid varchar,
   name_prefix varchar,
   firstname varchar,
   middle_name varchar,
   hm varchar,
   lastname varchar,
   name_suffix varchar,
   city varchar,
   comu_city varchar,
   state varchar,
   comu_state varchar,
   zipcode varchar,
   country varchar,
   comu_country varchar,
   accountbalance double,
   age integer,
   gender varchar,
   occupation varchar,
   industry varchar,
   lead_source varchar,
   product_interest varchar,
   education_level varchar,
   type varchar,
   isonline varchar,
   actts varchar,
   device_model_name varchar,
   expts varchar,
   account_number varchar,
   device_group varchar,
   campaignid varchar,
   apid varchar,
   acct_type varchar,
   income varchar,
   key_type varchar,
   app_version varchar,
   ct_enc_cust_id varchar,
   device_brand varchar,
   un_enc_cust_id varchar,
   key varchar,
   touchpoint_activity_date varchar,
   address varchar,
   device_os varchar,
   he varchar,
   channel_prediction varchar,
   best_send_time varchar,
   lead_category varchar,
   product_recommendation varchar,
   churn_score varchar,
   ltv varchar,
   product_holding array(varchar),
   product_closed array(varchar),
   product_closed_date array(varchar),
   card_variant_product_name array(varchar),
   card_type array(varchar),
   oid array(varchar),
   masked_card_no array(varchar),
   crmid varchar,
   primary_product varchar,
   secondary_product varchar,
   tertiary_product varchar,
   primary_channel varchar,
   secondary_channel varchar,
   tertiary_channel varchar,
   top_hr_primary_channel varchar,
   top_hr_secondary_channel varchar,
   top_hr_tertiary_channel varchar,
   aml_flag varchar,
   aml_risk_score varchar,
   available_balance_casa varchar,
   avg_balance_casa_30d varchar,
   avg_balance_casa_7d varchar,
   avg_daily_credit_transaction_amount_casa_30d varchar,
   avg_daily_credit_transaction_count_casa_30d varchar,
   avg_daily_debit_transaction_amount_casa_30d varchar,
   avg_daily_debit_transaction_count_casa_30d varchar,
   best_channel varchar,
   best_time_to_send varchar,
   birth_date varchar,
   campaign_details varchar,
   citizenship varchar,
   consent_for_auto_decisioning varchar,
   consent_for_biometric_data_collection varchar,
   consent_for_data_processing varchar,
   consent_for_data_sharing_affiliates varchar,
   consent_for_data_sharing_internal varchar,
   consent_for_data_sharing_service_providers varchar,
   consent_for_fraud_processing varchar,
   consent_for_geolocation_capture varchar,
   consent_for_legal_compliance varchar,
   consent_for_marketing_email varchar,
   consent_for_marketing_newsletter varchar,
   consent_for_marketing_phone varchar,
   consent_for_marketing_postal varchar,
   consent_for_marketing_sms varchar,
   consent_for_personalization varchar,
   consent_for_sensitive_data_processing varchar,
   consent_for_third_party_marketing varchar,
   correspondence_address_line1 varchar,
   correspondence_address_line2 varchar,
   correspondence_city varchar,
   correspondence_country_code varchar,
   correspondence_postal_code varchar,
   correspondence_state_province varchar,
   credit_transaction_amount_casa_30d varchar,
   credit_transaction_amount_casa_7d varchar,
   credit_transaction_amount_cc_30d varchar,
   credit_transaction_amount_cc_7d varchar,
   credit_transaction_amount_loans_30d varchar,
   credit_transaction_amount_loans_7d varchar,
   credit_transaction_count_casa_30d varchar,
   credit_transaction_count_casa_7d varchar,
   credit_transaction_count_cc_30d varchar,
   credit_transaction_count_cc_7d varchar,
   credit_transaction_count_loans_30d varchar,
   credit_transaction_count_loans_7d varchar,
   date_last_contacted varchar,
   date_last_responded varchar,
   debit_transaction_amount_casa_30d varchar,
   debit_transaction_amount_casa_7d varchar,
   debit_transaction_amount_cc_30d varchar,
   debit_transaction_amount_cc_7d varchar,
   debit_transaction_amount_loans_30d varchar,
   debit_transaction_amount_loans_7d varchar,
   debit_transaction_count_casa_30d varchar,
   debit_transaction_count_casa_7d varchar,
   debit_transaction_count_cc_30d varchar,
   debit_transaction_count_cc_7d varchar,
   debit_transaction_count_loans_30d varchar,
   debit_transaction_count_loans_7d varchar,
   email_campaign_responses_30d varchar,
   email_campaign_responses_7d varchar,
   email_verified varchar,
   ethnicity varchar,
   failed_campaign_contacts_30d varchar,
   failed_campaign_contacts_7d varchar,
   first_name varchar,
   fraud_flag varchar,
   fraud_risk_score varchar,
   highest_transaction_value_casa_30d varchar,
   highest_transaction_value_cc_30d varchar,
   highest_transaction_value_loans_30d varchar,
   high_risk_country_flag varchar,
   income_range varchar,
   kyc_customer_due_diligence_level varchar,
   kyc_next_review_date varchar,
   kyc_risk_level varchar,
   kyc_risk_score varchar,
   kyc_status varchar,
   kyc_verification_date varchar,
   last_name varchar,
   marital_status varchar,
   max_balance_casa_30d varchar,
   max_balance_casa_7d varchar,
   max_balance_cc_30d varchar,
   max_balance_cc_7d varchar,
   max_balance_loans_30d varchar,
   max_balance_loans_7d varchar,
   min_balance_casa_30d varchar,
   min_balance_casa_7d varchar,
   min_balance_cc_30d varchar,
   min_balance_cc_7d varchar,
   min_balance_loans_30d varchar,
   min_balance_loans_7d varchar,
   mobile_verified varchar,
   mother_maiden_name varchar,
   multi_channel_responder varchar,
   no_of_dependents varchar,
   occupation_code varchar,
   offers_count varchar,
   outstanding_balance_cc varchar,
   outstanding_balance_loans varchar,
   pep_flag varchar,
   pep_level varchar,
   permanent_address_line1 varchar,
   permanent_address_line2 varchar,
   permanent_address_status varchar,
   permanent_city varchar,
   permanent_country_code varchar,
   permanent_postal_code varchar,
   permanent_state_province varchar,
   phone_number varchar,
   phone_verified varchar,
   products_held_count varchar,
   product_holdings varchar,
   push_campaign_responses_30d varchar,
   push_campaign_responses_7d varchar,
   rcs_campaign_responses_30d varchar,
   rcs_campaign_responses_7d varchar,
   salutation varchar,
   sanction_flag varchar,
   sanction_status varchar,
   sms_campaign_responses_30d varchar,
   sms_campaign_responses_7d varchar,
   total_accounts varchar,
   total_actual_responses_received_30d varchar,
   total_actual_responses_received_7d varchar,
   total_available_credit_limit varchar,
   total_campaign_contacts_30d varchar,
   total_campaign_contacts_7d varchar,
   total_campaign_responses_30d varchar,
   total_campaign_responses_7d varchar,
   total_casa_accounts varchar,
   total_credit_card_accounts varchar,
   total_credit_count_90d varchar,
   total_credit_transaction_amount_90d varchar,
   total_debit_count_90d varchar,
   total_debit_transaction_amount_90d varchar,
   total_deposit_accounts varchar,
   total_loan_accounts varchar,
   total_no_of_conversions varchar,
   total_revenue varchar,
   total_spent varchar,
   webchannel_campaign_responses_30d varchar,
   webchannel_campaign_responses_7d varchar,
   whatsapp_campaign_responses_30d varchar,
   whatsapp_campaign_responses_7d varchar,
   masterid varchar,
   identitytype varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2,
   location = 's3a://<bucket-name>/<tenantId>/profile_data_sync_athena',
   max_commit_retry = 4
)

Table: source_conversions

CREATE TABLE minio.segmentdb_<tenantId>.source_conversions (
   campaignname varchar,
   campaigncode varchar,
   offercode varchar,
   offername varchar,
   customerid varchar,
   responsetype varchar,
   responsechannel varchar,
   responsedatetime timestamp(6),
   cost double,
   product varchar,
   revenue double,
   treatmentcode varchar,
   eventtype varchar,
   date varchar,
   time varchar,
   advid varchar,
   year varchar,
   month varchar,
   day varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2,
   location = 's3a://<bucket-name>/Segmentation/segmentdb_<tenantId>/source_conversions',
   max_commit_retry = 4
)

Table: source_transactions

CREATE TABLE minio.segmentdb_<tenantId>.source_transactions (
   mer_id varchar,
   cod_mcc varchar,
   cr_dr varchar,
   txn_amount double,
   txn_date timestamp(6),
   particulars varchar,
   encrypted_customerid varchar,
   advid varchar,
   year varchar,
   month varchar,
   day varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2,
   location = 's3a://<bucket-name>/Segmentation/segmentdb_<tenantId>/source_transactions',
   max_commit_retry = 4
)

Table: source_unicach

CREATE TABLE minio.segmentdb_<tenantId>.source_unicach (
   encrypted_customerid varchar,
   responsedatetime timestamp(6),
   actualreasoncode varchar,
   reasoncode varchar,
   responsechannel varchar,
   actualchannel varchar,
   vendor varchar,
   campaigncode varchar,
   campaignname varchar,
   offercode varchar,
   offername varchar,
   product varchar,
   treatmentcode varchar,
   success_flg varchar,
   ma_source varchar,
   cost double,
   advid varchar,
   year varchar,
   month varchar,
   day varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2,
   location = 's3a://<bucket-name>/Segmentation/segmentdb_<tenantId>/source_unicach',
   max_commit_retry = 4
)