@evanle96 Hive would treat the timestamp data to be in UTC format, and when we read it, it converts it from UTC to local time. But I understand that the actual data is already in America/Sao_Paulo tz. It'd be interesting to know how do we transform the epoch data in string format to timestamp in ORC? from_utc_timestamp(to_utc_timestamp(dt_afast_trabalho, 'America/Sao_Paulo'), 'America/Sao_Paulo') AS sao_paulo_time_corrected Can we try reading with the above transformation and see if the house field shows up as expected? ... View more

@AhXian I hope we have copied the bucket files correctly, and also ran MSCK REPAIR. Could you also run ANALYZE.. COMPUTE STATISTICS on the external table once. Make sure the DESCRIBE command on the external table reflects the number of data files correctly.  Please share describe and  "hdfs dfs -ls -R <externa table location>" o/p ... View more

@AhXian  1. Are you able to read data from the external table in Prod(step  4)? 2. In step 5, I meant for you to insert data into a new ACID table, which can be created using similar DDL as the original table. 3. After INSERT(step 5),  see if you could read the data from the new table. Ideally it should work ... View more

Yes, 5 steps mentioned above ... View more

In Hive ACID tables, the base/delta files are associated with a writeID. My assumption is the write ID has changed or got removed in the production HMS, so hive does not recognize the data file. So, we will have to follow the workaround that I mentioned in my last update.  ... View more

@AhXian the metadata is still not updated, as I see the following: ,numFiles ,0 ,numRows ,0 ,rawDataSize ,0 ,totalSize ,0 Let's try the following commands one after the other: MSCK REPAIR raw_zone_sit.jxtemp SYNC PARTITIONS; ANALYZE TABLE raw_zone_sit.jxtemp PARTITION (dl_created_yr='2023', dl_created_mth='12') COMPUTE STATISTICS; ANALYZE TABLE raw_zone_sit.jxtemp PARTITION (dl_created_yr='2023', dl_created_mth='12') COMPUTE STATISTICS for COLUMNS; See if the describe command still reflects 0 files. Also, could you collect the output of the following command from the backend metastore database? select * from NEXT_WRITE_ID where NWI_TABLE='jxtemp'; ... View more

@AhXian These ORC tables we are talking about, are they ACID? If we copy data files to ACID table location, and run ANALYZE...COMPUTE STATISTICS, that alone might not resolve the issue. We would like for you to share an example. Please share the following command output: describe formatted table_name partition(dl_created_yr='2023', dl_created_mth='12') &HDFS command o/p hdfs dfs -ls -R <partition location from the above command> ... View more

@ldylag You may find the DDLs under /opt/cloudera/parcels/CDH/lib/hive/scripts/metastore/upgrade/<db type> ... View more

@zhuodongLi It will be difficult to find the cause of the issue from the error message in the screenshot. It will be best if you could create a support case with the YARN app log of application_1719542914905_0002, and HS2 logs covering the job run period. ... View more