Snowflake to Hive data movement with partition

Question

We have a requirement to move data from Snowflake to Hive. I am able to unload data from snowflake to aws S3 and do and msck repair on Hive. But all records are coming as null in Hive. What could be the reason ? Is there anything wrong here . To check the parquet is created correctly , I read the Parquet file using Spark . I am able to read the parquet file.

##Snowflake create or replace stage dev_zone.DAILY_LOG url= 's3://myc-mlb-alpha-us-east-1-drg-322t232/hive/rs_hive_008_test1' storage_integration = DEV_HIVE_INTEGRATION file_format = (type = 'parquet') ENCRYPTION = (TYPE = 'AWS_SSE_S3');

copy into @dev_zone.DAILY_LOG from (select * from dev_zone.DAILY_LOG limit 100) partition by  ('as_on_date=' ||as_on_date);

##Hive 
CREATE EXTERNAL TABLE dev_zone.DAILY_LOG(
dim_id decimal(38,0),
card_type string,
type string,
cntry string,
PARTITIONED BY (
  as_on_date date)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://myc-mlb-alpha-us-east-1-drg-322t232/hive/rs_hive_008_test1'

Sachin Sukumaran Sachin Sukumaran · Accepted Answer · 2021-02-08T03:33:19

What I missed was to add header = true

copy into @dev_zone.DAILY_LOG from (select * from dev_zone.DAILY_LOG limit 100) partition by  ('as_on_date=' ||as_on_date) header = true;

Snowflake to Hive data movement with partition

1 Answers