FEATURE_​OMNI_​063 review

copyFEATURE_OMNI_063to '/Users/toannk986/abb/temp/_tmp/FEATURE_OMNI_063_$${Sd_yyyymmdd}$$.parquet'

Bảng lookup model - phân khúc

Rename các trường khác theo mã biếnALTER TABLE FEATURE_OMNI_063RENAME COLUMN TYPE TO FEATURE_OMNI_0839;ALTER TABLE FEATURE_OMNI_063RENAME COLUMN PLATFORM TO FEATURE_OMNI_0840;ALTER TABLE FEATURE_OMNI_063RENAME COLUMN MODEL TO FEATURE_OMNI_0841;ALTER TABLE FEATURE_OMNI_063RENAME COLUMN VENDOR TO FEATURE_OMNI_0842;CREATE OR REPLACE TABLE FEATURE_OMNI_063 ASSELECT DAY_ID, CUSTOMER_ID, FEATURE_OMNI_0842, FEATURE_OMNI_0841, FEATURE_OMNI_0840, FEATURE_OMNI_0839FROM FEATURE_OMNI_063;

Tạo lại connection DB

-- Load file parquetCREATE OR REPLACE TABLE FEATURE_OMNI_063 ASSELECT *FROM read_parquet("/Users/toannk986/Downloads/device.parquet")

Map giá trị trong các biến FEATURE_OMNI_0840 và FEATURE_OMNI_0842 theo bảng lookup, convert data type sang integer

CREATE OR REPLACE TABLE FEATURE_OMNI_0840_LOOKUP ASSELECT *FROM read_xlsx('$${Svaluelookup_filepath}$$', sheet='FEATURE_OMNI_0840');CREATE OR REPLACE TABLE FEATURE_OMNI_0842_LOOKUP ASSELECT *FROM read_xlsx('$${Svaluelookup_filepath}$$', sheet='FEATURE_OMNI_0842');

valuelookup_filepath

Thêm trường DAY_ID và TYPEALTER TABLE FEATURE_OMNI_063ADD COLUMN IF NOT EXISTS DAY_ID VARCHAR;ALTER TABLE FEATURE_OMNI_063ADD COLUMN IF NOT EXISTS TYPE INT;UPDATE FEATURE_OMNI_063SET DAY_ID = strftime(current_date, '%Y-%m-%d');

Merge db, map model sản phẩm với phân khúc