COUNTRIES

The COUNTRIES table contains description data for countries.

Unique Key

Incoming Joins

Table creation script

Snowflake

Table creation

create OR REPLACE stage MR_INPUT_RAW;
create OR REPLACE table COUNTRIES(
AS_OF_DATE DATE NOT NULL,
COUNTRY_CODE STRING NOT NULL DEFAULT 'N/A',
REGION STRING NOT NULL DEFAULT 'N/A',
SUB_REGION STRING NOT NULL DEFAULT 'N/A',
COUNTRY STRING NOT NULL DEFAULT 'N/A',
LATITUDE STRING NOT NULL DEFAULT 'N/A',
LONGITUDE STRING NOT NULL DEFAULT 'N/A',
primary key (AS_OF_DATE, COUNTRY_CODE)
);

Table population from files (optional)

If you want to populate the table from CSV files, you can run the following scripts (this example assumes that you want to load the files Countries.csv present in the folders 2023-09-26, 2023-09-27 and 2023-09-28):

create OR REPLACE file format CSV_GZIP
	TYPE = CSV
    COMPRESSION = GZIP
    SKIP_HEADER = 1
    comment = 'File format to import MR data';
create OR REPLACE file format CSV_FORMAT
TYPE = CSV
COMPRESSION = AUTO
SKIP_HEADER = 1
comment = 'File format to import MR data';
PUT file://<full path to you data folder>/2023-09-26/Countries.csv @MR_INPUT_RAW/2023-09-26;
PUT file://<full path to you data folder>/2023-09-27/Countries.csv @MR_INPUT_RAW/2023-09-27;
PUT file://<full path to you data folder>/2023-09-28/Countries.csv @MR_INPUT_RAW/2023-09-28;
copy into COUNTRIES from (
select
$1,
$2,
ifnull($3, 'N/A'),
ifnull($4, 'N/A'),
ifnull($5, 'N/A'),
ifnull($6, 'N/A'),
ifnull($7, 'N/A')
from @MR_INPUT_RAW (pattern => './Countries..csv.gz' , FILE_FORMAT => 'CSV_GZIP'));