The following code works unless you change the bucketed column to Id or AccountType. When using those columns the job processes without any errors but some of the rows are dropped or ignored and do not end up in the accounts table. I started out using AccountType and 1.9MM rows were dropped every time I ran the job (20 - 30 times). I changed to Id after troubleshooting and eliminating other issues and found that only 32k rows were dropped. Finally I tried bucketing on Created (timestamp) and all rows were loaded into accounts table. Looking at the data that was skipped I could not see any pattern that would point to a specific bucket file.
DROP TABLE IF EXISTS ingest.txt_accounts;
CREATE TABLE IF NOT EXISTS ingest.txt_accounts (
Id BIGINT
,Created TIMESTAMP
,Modified TIMESTAMP
,Status VARCHAR(50)
,ActivationDate TIMESTAMP
,StatusReason VARCHAR(50)
,ApplicationId BIGINT
,IsFraud INT
,LimitPackage VARCHAR(50)
,AccountType VARCHAR(50)
,IsEmployeeAccount INT
,IsOneTimeFeePaid INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" STORED AS TEXTFILE
TBLPROPERTIES("skip.header.line.count"="1");
LOAD DATA INPATH 'adl://datalake.azuredatalakestore.net/LandingZone/Accounts'
OVERWRITE INTO TABLE ingest.txt_accounts;
DROP TABLE IF EXISTS ingest.accounts;
CREATE TABLE IF NOT EXISTS ingest.accounts (
Id BIGINT
,Created TIMESTAMP
,Modified TIMESTAMP
,Status VARCHAR(50)
,ActivationDate TIMESTAMP
,StatusReason VARCHAR(50)
,ApplicationId BIGINT
,IsFraud INT
,LimitPackage VARCHAR(50)
,AccountType VARCHAR(50)
,IsEmployeeAccount INT
,IsOneTimeFeePaid INT
)
CLUSTERED BY (Created) INTO 10 BUCKETS
STORED AS ORC
TBLPROPERTIES("transactional" = "true");
INSERT INTO TABLE ingest.accounts
SELECT
Id
,Created
,Modified
,Status
,ActivationDate
,StatusReason
,ApplicationId
,IsFraud
,LimitPackage
,AccountType
,IsEmployeeAccount
,IsOneTimeFeePaid
FROM ingest.txt_accounts;
