| set hive.strict.checks.type.safety=false; |
| |
| CREATE TABLE batch1_19671_fin( |
| pts char(15), |
| CoNameOrCIK char(60) |
| ); |
| |
| CREATE TABLE dimCompany( |
| sk_CompanyId bigint, |
| name char(60), |
| companyId bigint |
| ); |
| |
| CREATE TABLE Financial ( |
| SK_CompanyID BIGINT, |
| FI_YEAR NUMERIC(4) , |
| FI_QTR char(10) , |
| FI_QTR_START_DATE DATE, |
| cId TINYINT, |
| cTimeStamp TIMESTAMP, |
| cDecimal DECIMAL(38,18), |
| cDouble DOUBLE, |
| cFloat FLOAT, |
| cBigInt BIGINT, |
| cInt INT, |
| cSmallInt SMALLINT, |
| cTinyint TINYINT, |
| cBoolean BOOLEAN |
| ); |
| |
| CREATE TABLE FinancialPart ( |
| SK_CompanyID BIGINT, |
| FI_YEAR NUMERIC(4) , |
| FI_QTR char(10) , |
| FI_QTR_START_DATE DATE, |
| cId TINYINT, |
| cTimeStamp TIMESTAMP, |
| cDecimal DECIMAL(38,18), |
| cDouble DOUBLE, |
| cFloat FLOAT, |
| cBigInt BIGINT, |
| cInt INT, |
| cSmallInt SMALLINT, |
| cTinyint TINYINT, |
| cBoolean BOOLEAN |
| ) |
| PARTITIONED BY (country string); |
| |
| -- This insert will add stats with null bit-vector as there is no records matching |
| INSERT INTO FinancialPart partition (country='country1') ( |
| SK_CompanyID, |
| FI_YEAR, |
| FI_QTR, |
| FI_QTR_START_DATE, |
| cId, |
| cTimeStamp, |
| cDecimal, |
| cDouble, |
| cFloat, |
| cBigInt, |
| cInt, |
| cSmallInt, |
| cTinyint, |
| cBoolean |
| ) |
| SELECT |
| DimCompany.SK_CompanyID, |
| 2021, |
| 'third', |
| to_date('2021-11-21'), |
| 1, |
| '2017-11-07 09:02:49.999999999', |
| 12345678901234567890.123456789012345678, |
| 1.79e308, |
| 3.4e38, |
| 1234567890123456789, |
| 1234567890, |
| 12345, |
| 123, |
| TRUE |
| FROM |
| batch1_19671_fin finwire_fin |
| JOIN DimCompany ON ( |
| finwire_fin.CoNameOrCIK = DimCompany.CompanyID ) |
| UNION |
| SELECT |
| DimCompany.SK_CompanyID, |
| 2021, |
| 'first', |
| to_date('2021-01-21'), |
| 1, |
| '2017-11-07 09:02:49.999999999', |
| 12345678901234567890.123456789012345678, |
| 1.79e308, |
| 3.4e38, |
| 1234567890123456789, |
| 1234567890, |
| 12345, |
| 123, |
| TRUE |
| FROM |
| batch1_19671_fin finwire_fin |
| JOIN DimCompany ON ( |
| finwire_fin.CoNameOrCIK = DimCompany.Name); |
| |
| desc formatted FinancialPart partition(country='country1') SK_CompanyID; |
| |
| -- This insert will try to merge the existing bit vector which is null |
| INSERT INTO FinancialPart partition (country) values |
| ( |
| 1099511627778, |
| 1967, |
| 'second', |
| to_date('1967-04-01'), |
| 1, |
| '2017-11-07 09:02:49.999999999', |
| 12345678901234567890.123456789012345678, |
| 1.79e308, |
| 3.4e38, |
| 1234567890123456789, |
| 1234567890, |
| 12345, |
| 123, |
| TRUE, |
| 'country3' |
| ); |
| |
| desc formatted FinancialPart partition(country='country1') SK_CompanyID; |
| desc formatted FinancialPart partition(country='country3') SK_CompanyID; |
| |
| -- This insert will add stats with null bit-vector as there is no records matching |
| INSERT INTO Financial ( |
| SK_CompanyID, |
| FI_YEAR, |
| FI_QTR, |
| FI_QTR_START_DATE, |
| cId, |
| cTimeStamp, |
| cDecimal, |
| cDouble, |
| cFloat, |
| cBigInt, |
| cInt, |
| cSmallInt, |
| cTinyint, |
| cBoolean |
| ) |
| SELECT |
| DimCompany.SK_CompanyID, |
| 2021, |
| 'third', |
| to_date('2021-11-21'), |
| 1, |
| '2017-11-07 09:02:49.999999999', |
| 12345678901234567890.123456789012345678, |
| 1.79e308, |
| 3.4e38, |
| 1234567890123456789, |
| 1234567890, |
| 12345, |
| 123, |
| TRUE |
| FROM |
| batch1_19671_fin finwire_fin |
| JOIN DimCompany ON ( |
| finwire_fin.CoNameOrCIK = DimCompany.CompanyID ) |
| UNION |
| SELECT |
| DimCompany.SK_CompanyID, |
| 2021, |
| 'first', |
| to_date('2021-01-21'), |
| 1, |
| '2017-11-07 09:02:49.999999999', |
| 12345678901234567890.123456789012345678, |
| 1.79e308, |
| 3.4e38, |
| 1234567890123456789, |
| 1234567890, |
| 12345, |
| 123, |
| TRUE |
| FROM |
| batch1_19671_fin finwire_fin |
| JOIN DimCompany ON ( |
| finwire_fin.CoNameOrCIK = DimCompany.Name); |
| |
| desc formatted Financial FI_QTR; |
| |
| -- This insert will try to merge the existing bit vector which is null |
| INSERT INTO Financial values |
| ( |
| 1099511627778, |
| 1967, |
| 'second', |
| to_date('1967-04-01'), |
| 1, |
| '2017-11-07 09:02:49.999999999', |
| 12345678901234567890.123456789012345678, |
| 1.79e308, |
| 3.4e38, |
| 1234567890123456789, |
| 1234567890, |
| 12345, |
| 123, |
| TRUE |
| ); |
| |
| desc formatted Financial FI_QTR; |
| |