Definición de Tablas Maestras (DDL)

Este archivo contiene las sentencias SQL para la creación de las tablas de referencia utilizadas en el proceso de normalización de la capa Silver.

1. Catálogo de Países (ISO 3166)

Esta tabla permite unificar la procedencia de los datos mediante el ID de Google o el nombre del país, convirtiéndolos siempre al estándar alpha_2.

CREATE TABLE IF NOT EXISTS `prj-corp-pre-dwh.nw_dwh_app_silver.master_country`
(
  name STRING NOT NULL OPTIONS(description="Nombre del pais en ingles"),
  alpha_2 STRING(2) OPTIONS(description="Codigo identificativo de 2 digitos"),
  criteria_id INT64 OPTIONS(description="Codigo de indentificacion interno"),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP() OPTIONS(description="Fecha de la ultima modificacion")
)
OPTIONS(description="Tabla maestra de paises");

1.1. Carga de datos

CREATE OR REPLACE EXTERNAL TABLE `prj-corp-pre-dwh.nw_dwh_app_silver.tmp_country_gcs`
(
  name STRING,
  alpha_2 STRING,         
  alpha_3 STRING,
  country_code STRING,
  iso_3166_2 STRING,
  region STRING,
  sub_region STRING,
  intermediate_region STRING,
  region_code STRING,
  sub_region_code STRING,
  intermediate_region_code STRING,
  name_es STRING,
  criteria_id INT64
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://apps-etls-pre-storage/common/ISO_3166.csv'],
  skip_leading_rows = 1,
  allow_quoted_newlines = true
);

INSERT INTO `prj-corp-pre-dwh.nw_dwh_app_silver.country` (name, alpha_2, criteria_id)
SELECT 
    name, 
    alpha_2, 
    criteria_id 
FROM `prj-corp-pre-dwh.nw_dwh_app_silver.tmp_country_gcs`;

DROP TABLE `prj-corp-pre-dwh.nw_dwh_app_silver.tmp_country_gcs`;

Si es la primera vez que se cargan datos, es necesario corregir Namibia y añadir el país "Unknown".

UPDATE `prj-corp-pre-dwh.nw_dwh_app_silver.master_country`
SET alpha_2 = 'NA'
WHERE name = 'Namibia';

INSERT INTO `prj-corp-pre-dwh.nw_dwh_app_silver.cmaster_country` (criteria_id, name, alpha_2)
VALUES ('Unknown', 'UN', NULL);

2. Catálogo de Sinónimos

Esta tabla es el motor de la normalización de nombres de columnas. Permite mapear diversas fuentes de datos a un estándar interno.

CREATE TABLE IF NOT EXISTS `prj-corp-pre-dwh.nw_dwh_app_silver.field_catalog` (
  normalized_name STRING NOT NULL 
    OPTIONS(description="Nombre estándar interno"),
  
  domain STRING NOT NULL  
    OPTIONS(description="Origen del dato"),
  
  synonyms ARRAY<STRING> 
    OPTIONS(description="Lista de nombres equivalentes que pueden aparecer en los archivos de origen"),
  
  data_type STRING NOT NULL 
    OPTIONS(description="Tipo de dato de origen"),
  
  default_strategy STRING NOT NULL 
    OPTIONS(description="Estrategia ante nulos o errores"),
  
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP() 
    OPTIONS(description="Fecha y hora de la última actualización del registro"),
  
  updated_by STRING 
    OPTIONS(description="Usuario que realizó la última modificación")
)
OPTIONS(
  description="Tabla maestra para la normalización de campos y mapeo de sinónimos entre diferentes fuentes de datos."
);

1.1. Carga de datos

INSERT INTO `prj-corp-pre-dwh.nw_dwh_app_silver.field_catalog` 
(normalized_name, domain, synonyms, data_type, default_strategy) 
VALUES
('date', '*', ['date', 'Date', 'segments.date', 'stat_time_day', 'time', 'Day', 'dt', 'units.date', 'stat_datetime', 'Order Charged Date', 'Begin Date', 'End Date', 'Event Date'], 'DATE', 'REJECT_ROW'),
('date_partition', '*', ['partition_date'], 'DATE', 'REJECT_ROW'),
('date_app_purchase', 'common', ['buy_date'], 'DATE', 'SET_UNKNOWN'),
('app_code', '*', ['app_code', 'App', 'App Code', 'application name', 'app_name', 'Zone', 'Source game name', 'app', 'inmobiAppName'], 'STRING', 'SET_UNKNOWN'),
('app_code', 'common', ['app_code', 'App Code'], 'STRING', 'SET_UNKNOWN'),
('bundle_id', '*', ['bundle_id', 'bundleId', 'app.bundleId', 'MOBILE_APP_RESOLVED_ID', 'Package Name', 'Package name', 'appBundleId', 'adamId', 'Product id', 'Product ID'], 'STRING', 'SET_UNKNOWN'),
('bundle_id', 'common', ['bundle_id'], 'STRING', 'SET_UNKNOWN'),
('os', '*', ['platform', 'appPlatform', 'app.platform', 'Supported Platforms', 'os', 'campaign.appCampaignSetting.appStore'], 'STRING', 'SET_UNKNOWN'),
('os', 'common', ['os'], 'STRING', 'SET_UNKNOWN'),
('country_code', '*', ['country', 'country_code', 'countryOrRegion', 'units.country', 'countryCode', 'CountryCode', 'Country code', 'Buyer Country', 'Country of Buyer', 'Country Of Sale', 'Country Code'], 'STRING', 'SET_UNKNOWN'),
('country_code', 'common', ['alpha-2'], 'STRING', 'SET_UNKNOWN'),
('country_criterion_id', '*', ['geographicView.countryCriterionId', 'countryCriterionId', 'userLocationView.countryCriterionId'], 'INT64', 'SET_UNKNOWN'),
('country_criterion_id', 'common', ['criteria_id'], 'INT64', 'SET_UNKNOWN'),
('currency_code', '*', ['currency', 'Currency', 'currency_code', 'Merchant Currency', 'Currency of Sale', 'Buyer Currency', 'Currency of Proceeds', 'Proceeds Currency', 'account_currency', 'budget_currency'], 'STRING', 'SET_UNKNOWN'),
('currency_code', 'common', ['currency'], 'STRING', 'SET_UNKNOWN'),
('account_id', '*', ['account_id'], 'STRING', 'SET_UNKNOWN'),
('video_views', '*', ['views', 'video_view'], 'INT64', 'SET_ZERO'),
('video_completes', '*', ['completes', 'videoCompletedDelivered'], 'INT64', 'SET_ZERO'),
('network_name_ua', 'ua', ['adChannelType', 'traffic_source', 'ua_network'], 'STRING', 'SET_UNKNOWN'),
('network_name_ads', 'ads', ['network_name_used_by_admob', 'PublisherNetworkId', 'PotensusNetworkId'], 'STRING', 'SET_UNKNOWN'),
('campaign_id', '*', ['campaign.id', 'campaign_id'], 'STRING', 'SET_UNKNOWN'),
('campaign_name', '*', ['campaign_name', 'campaign.resourceName', 'campaign.name'], 'STRING', 'SET_UNKNOWN'),
('ad_group_name', '*', ['ad_group_name', 'adGroupName', 'adset_name', 'ad_set_name'], 'STRING', 'SET_UNKNOWN'),
('ad_group_id', '*', ['ad_group.id', 'adGroupId', 'ad_group_id'], 'STRING', 'REJECT_ROW'),
('placement_name', '*', ['placement_name', 'placement name', 'placement', 'placement id', 'placement reference id'], 'STRING', 'SET_UNKNOWN'),
('ad_format', '*', ['adFormat', 'adType', 'format', 'adUnit.format', 'Ad_type', 'InventoryFormat', 'ad_type', 'adUnitType', 'adType', 'ad_slot_type', 'ad_format'], 'STRING', 'SET_UNKNOWN'),
('ad_space', 'ads', ['ad_space', 'Ad Space', 'adUnit.code', 'mediation_group_label'], 'STRING', 'SET_UNKNOWN'),
('ad_name', 'ads', ['ad_name'], 'STRING', 'SET_UNKNOWN'),
('spend_ua', 'ua', ['spend', 'localSpend', 'metrics.costMicros'], 'NUMERIC', 'REJECT_ROW'),
('impressions_ua', 'ua', ['impressions', 'allInstalls_impressions', 'campaign_impressions', 'metrics.impressions'], 'INT64', 'REJECT_ROW'),
('clicks_ua', 'ua', ['clicks', 'taps', 'link_click', 'metrics.clicks'], 'INT64', 'REJECT_ROW'),
('clicks_ads', 'ads', ['clicks', 'Clicks', 'clicksReceived', 'fb_ad_network_click', 'Click'], 'INT64', 'REJECT_ROW'),
('installs_paid', 'ua', ['installs', 'mobile_app_install', 'newDownloads', 'metrics.conversions', 'omni_app_install', 'conversion'], 'INT64', 'REJECT_ROW'),
('conversion_category', '*', ['segments.conversionActionCategory'], 'STRING', 'SET_UNKNOWN'),
('requests_ads', 'ads', ['request', 'ad_request', 'adExchangeTotalRequests', 'fb_ad_network_request'], 'INT64', 'REJECT_ROW'),
('impressions_ads', 'ads', ['impressions', 'Impressions', 'fb_ad_network_imp', 'impressionsDelivered', 'adImpressions'], 'INT64', 'REJECT_ROW'),
('fills_ads', 'ads', ['fills', 'fb_ad_network_filled_request'], 'INT64', 'REJECT_ROW'),
('revenue_ads', 'ads', ['fb_ad_network_revenue', 'publisherRevenueUsd', 'revenue_ads_eur', 'earnings', 'est_revenue', 'PublisherCPMAndCPCRevenue', 'revenue'], 'NUMERIC', 'REJECT_ROW'),
('revenue_iap', 'adapty', ['Revenue'], 'NUMERIC', 'REJECT_ROW'),
('impressions_blocked', 'ads', ['fb_ad_network_no_fill', 'fb_ad_network_no_bid'], 'INT64', 'REJECT_ROW'),
('revenue_protected', 'ads', ['revenue_impact', 'revenue_protected'], 'NUMERIC', 'REJECT_ROW'),
('revenue_store_total', '*', ['Revenue', 'Total', 'Customer Price', 'Charged Amount', 'Total Proceeds', 'Item Price'], 'NUMERIC', 'REJECT_ROW'),
('revenue_store_net', '*', ['Revenue Net', 'Total Net', 'Developer Proceeds', 'Proceeds', 'revenue_net'], 'NUMERIC', 'REJECT_ROW'),
('installs_store', '*', ['Installs', 'Units', 'newDownloads', 'redownloads', 'Daily User Installs', 'Daily Device Installs'], 'INT64', 'REJECT_ROW'),
('trials_new', 'adapty', ['New Trials'], 'INT64', 'REJECT_ROW'),
('trials_active', 'adapty', ['Active Trials'], 'INT64', 'REJECT_ROW'),
('subscriptions_new', 'adapty', ['New Subscriptions'], 'INT64', 'REJECT_ROW'),
('subscriptions_active', 'adapty', ['Active Subscriptions'], 'INT64', 'REJECT_ROW'),
('subscriptions_cancelled', '*', ['Subscriptions Renewal Cancelled', 'Trials Renewal Cancelled', 'Days Canceled', 'cancelled'], 'INT64', 'REJECT_ROW');

3. Catálogo de Aplicaciones

Esta tabla permite unificar la procedencia de los datos mediante el app_code o el bundle_id.

CREATE TABLE IF NOT EXISTS `prj-corp-pre-dwh.nw_dwh_app_silver.master_app`
(
  app_code STRING NOT NULL OPTIONS(description="Código interno de la aplicación"),
  bundle_id STRING OPTIONS(description="ID interno de la aplicación"),
  product_name STRING OPTIONS(description="Nombre de la aplicación"),
  app_store_id STRING OPTIONS(description="ID de la store"),
  sku STRING OPTIONS(description="Stock Keeping Unit - Identificador único de inventario"),
  chartboost_app_id STRING OPTIONS(description="ID interno de chartboost"),
  unity_game_id STRING OPTIONS(description="ID interno de unity"),
  liftoff_app_id STRING OPTIONS(description="ID interno de liftoff"),
  type STRING NOT NULL OPTIONS(description="Tipo de aplicación (ej. Juego, Herramienta)"),
  store STRING OPTIONS(description="Tienda de la aplicación (Google Play, App Store)"),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP() OPTIONS(description="Fecha de última modificación"),
  updated_by STRING DEFAULT SESSION_USER() OPTIONS(description="Usuario que lo modificó"),
)
PARTITION BY _PARTITIONDATE
OPTIONS(description="Tabla maestra de aplicaciones");

1. Catálogo de Países (ISO 3166)​

1.1. Carga de datos​

2. Catálogo de Sinónimos​

1.1. Carga de datos​

3. Catálogo de Aplicaciones​

1. Catálogo de Países (ISO 3166)

1.1. Carga de datos

2. Catálogo de Sinónimos

1.1. Carga de datos

3. Catálogo de Aplicaciones