| --store timestamps as strings for copying into different schemas |
| create table parquet_timestamp_staging_2 (i int, s string); |
| insert into parquet_timestamp_staging_2 |
| values |
| (0, '0001-01-01'), |
| (1, '1677-09-21 00:12:43.145224192'), |
| (2, '1969-12-31 23:59:59.99999999999999999'), |
| (3, '1970-01-01 00:00:00'), |
| (4, '2013-09-27 01:36:18.000000001'), |
| (5, '2018-01-02 13:14:15.678999'), |
| (6, '2262-04-11 23:47:16.854775807'), |
| (7, '9999-12-31 23:59:59.999999999999'); |
| |
| |
| --make int64 table with microsecond granularity |
| set hive.parquet.write.int64.timestamp=true; |
| set hive.parquet.timestamp.time.unit=micros; |
| create table parquet_int64 (i int, ts timestamp) stored as parquet; |
| insert into parquet_int64 select i, cast (s as timestamp) from parquet_timestamp_staging_2; |
| |
| --make int96 table |
| set hive.parquet.write.int64.timestamp=false; |
| create table parquet_int96 (i int, ts timestamp) stored as parquet; |
| insert into parquet_int96 select i + 10, cast (s as timestamp) from parquet_timestamp_staging_2; |
| |
| --join int64 and int96 tables |
| select parquet_int64.i, parquet_int64.ts, parquet_int96.ts |
| from parquet_int64 |
| join parquet_int96 |
| on parquet_int64.ts = parquet_int96.ts |
| order by parquet_int64.i; |
| |
| |
| --create table with mixed int64/milli and int96 values |
| set hive.parquet.write.int64.timestamp=true; |
| set hive.parquet.timestamp.time.unit=millis; |
| create table parquet_mixed_timestamp as select * from parquet_int64; |
| set hive.parquet.write.int64.timestamp=false; |
| insert into parquet_mixed_timestamp select i + 10, cast (s as timestamp) from parquet_timestamp_staging_2; |
| |
| select * from parquet_mixed_timestamp order by i; |
| select * from parquet_mixed_timestamp where ts > cast ('2200-01-01 00:00:00.00' as timestamp) order by i; |
| select * from parquet_mixed_timestamp where ts < cast ('1900-12-31 23:59:59.9999999999' as timestamp) order by i; |
| select count(*) from parquet_mixed_timestamp where ts = cast ('1970-01-01 00:00:00.00' as timestamp); |
| --join mixed table and int64 table |
| select * |
| from parquet_mixed_timestamp |
| join parquet_int64 |
| on parquet_mixed_timestamp.ts = parquet_int64.ts |
| order by parquet_mixed_timestamp.i; |