You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hudi.apache.org by GitBox <gi...@apache.org> on 2020/09/27 16:24:25 UTC
[GitHub] [hudi] therealppk opened a new issue #2123: Timestamp not parsed correctly on Athena
therealppk opened a new issue #2123:
URL: https://github.com/apache/hudi/issues/2123
The data type of the timestamp columns are flawed inspite of being correct in the DataFrame.
The schema of the DataFrame is:
```
root
|-- branch_id: long (nullable = true)
|-- comment: string (nullable = true)
|-- created_at: timestamp (nullable = true)
...
```
The output of the DataFrame show():
```
--------------+--------------------+-----------------------+
|branch_id| comment| created_at|
--------------+--------------------+-----------------------+
| 13501| |2017-05-09 08:21:35|
| 14081| |2017-05-09 08:53:29|
...
--------------+--------------------+-----------------------+
```
The output in Athena after storing the DataFrame in hudi format.
```
--------------+--------------------+-----------------------+
|branch_id| comment| created_at|
--------------+--------------------+-----------------------+
| 13501| | +49134-01-07 05:30:00.000|
| 14081| | +49153-08-06 07:20:00.000|
...
--------------+--------------------+-----------------------+
```
Code to write the DataFrame "main_df" in Hudi format:
```
hudi_options = {
'hoodie.table.name': table.name,
'hoodie.datasource.write.recordkey.field': table.primary_key,
'hoodie.datasource.write.partitionpath.field': partition_by,
'hoodie.datasource.write.table.name': table.name,
'hoodie.datasource.write.operation': "upsert",
'hoodie.datasource.write.precombine.field': "ts_ms",
'hoodie.upsert.shuffle.parallelism': 2,
'hoodie.insert.shuffle.parallelism': 2
}
main_df.write.format("hudi"). \
options(**hudi_options). \
mode("append"). \
save(desturl)
```
The Issue is that Athena recognises int96 at timestamps and not int64 which is given by hudi. What is the fix for this?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] nsivabalan commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
nsivabalan commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-766439219
@satishkotha : when you get a chance, can you please follow up on this.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] satishkotha commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
satishkotha commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-701154113
This is a bit complicated. Hudi uses spark converters to convert dataframe type into parquet type. Spark SchemaConverters converts timestamp to [int64](https://github.com/apache/spark/blob/master/external/avro/src/main/scala/org/apache/spark/sql/avro/SchemaConverters.scala#L164) with logical type 'TIMESTAMP_MICROS'.
This is because int96 is no longer supported in [parquet](https://issues.apache.org/jira/browse/PARQUET-1883), especially parquet-avro module. In general, int96 is discouraged going forward.
To make timestamp work, we had to
1) Change query engines to support reading parquet logical type. Example for [presto](https://github.com/prestodb/presto/pull/15074/files). We did similar change for Hive. You probably need similar change in Athena
2) Change DLASync/HiveSync to convert logical type TIMESTAMP_MICROS as hive type 'timestamp'. [PR here](https://github.com/apache/hudi/pull/2129)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837229546
@jackye1995 Is it necessary to change something to make it work? Because I still have a problem.
My code:
`spark = SparkSession \
.builder \
.appName("data_ingestion_incremental_dev") \
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer") \
.config("spark.sql.hive.convertMetastoreParquet", "false") \
.config("spark.sql.session.timeZone", "America/Sao_Paulo") \
.config("spark.hadoop.mapred.output.committer.class","com.appsflyer.spark.DirectOutputCommitter") \
.config("mapreduce.fileoutputcommitter.marksuccessfuljobs", "false") \
.config("spark.dynamicAllocation.enabled", "true") \
.enableHiveSupport() \
.getOrCreate()
table.write \
.format("org.apache.hudi") \
.option("hoodie.table.name", f"{data_source}_{table_name}") \
.option("hoodie.datasource.write.table.type", "COPY_ON_WRITE") \
.option("hoodie.datasource.write.operation", "INSERT") \
.option("hoodie.datasource.write.recordkey.field", pk_column) \
.option("hoodie.datasource.write.precombine.field", incremental_column) \
.option("hoodie.datasource.write.keygenerator.class", "org.apache.hudi.keygen.CustomKeyGenerator") \
.option("hoodie.datasource.write.partitionpath.field", "year:SIMPLE, month:SIMPLE, day:SIMPLE") \
.option("hoodie.datasource.hive_sync.enable", True) \
.option("hoodie.datasource.hive_sync.support_timestamp", True) \
.option("hoodie.datasource.hive_sync.database", database_athena) \
.option("hoodie.datasource.hive_sync.table", f"{data_source}_{table_name}") \
.option("hoodie.datasource.hive_sync.partition_fields", "year, month, day") \
.option("hoodie.datasource.hive_sync.partition_extractor_class", "org.apache.hudi.hive.MultiPartKeysValueExtractor") \
.option("hoodie.consistency.check.enabled", True) \
.option("hoodie.parquet.max.file.size", 1073741824) \
.option("hoodie.parquet.small.file.limit", 629145600) \
.option("hoodie.parquet.compression.codec", "snappy") \
.option("hoodie.copyonwrite.insert.split.size", 500000) \
.option("hoodie.copyonwrite.record.size.estimate", 1024) \
.mode("overwrite") \
.save(path_s3)
`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837229546
@jackye1995 Is it necessary to change something to make it work? Because I still have a problem.
My code:
`spark = SparkSession \
.builder \
.appName("data_ingestion_incremental_dev") \
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer") \
.config("spark.sql.hive.convertMetastoreParquet", "false") \
.config("spark.sql.session.timeZone", "America/Sao_Paulo") \
.config("spark.hadoop.mapred.output.committer.class","com.appsflyer.spark.DirectOutputCommitter") \
.config("mapreduce.fileoutputcommitter.marksuccessfuljobs", "false") \
.config("spark.dynamicAllocation.enabled", "true") \
.enableHiveSupport() \
.getOrCreate()`
`table.write \
.format("org.apache.hudi") \
.option("hoodie.table.name", f"{data_source}_{table_name}") \
.option("hoodie.datasource.write.table.type", "COPY_ON_WRITE") \
.option("hoodie.datasource.write.operation", "INSERT") \
.option("hoodie.datasource.write.recordkey.field", pk_column) \
.option("hoodie.datasource.write.precombine.field", incremental_column) \
.option("hoodie.datasource.write.keygenerator.class", "org.apache.hudi.keygen.CustomKeyGenerator") \
.option("hoodie.datasource.write.partitionpath.field", "year:SIMPLE, month:SIMPLE, day:SIMPLE") \
.option("hoodie.datasource.hive_sync.enable", True) \
.option("hoodie.datasource.hive_sync.support_timestamp", True) \
.option("hoodie.datasource.hive_sync.database", database_athena) \
.option("hoodie.datasource.hive_sync.table", f"{data_source}_{table_name}") \
.option("hoodie.datasource.hive_sync.partition_fields", "year, month, day") \
.option("hoodie.datasource.hive_sync.partition_extractor_class", "org.apache.hudi.hive.MultiPartKeysValueExtractor") \
.option("hoodie.consistency.check.enabled", True) \
.option("hoodie.parquet.max.file.size", 1073741824) \
.option("hoodie.parquet.small.file.limit", 629145600) \
.option("hoodie.parquet.compression.codec", "snappy") \
.option("hoodie.copyonwrite.insert.split.size", 500000) \
.option("hoodie.copyonwrite.record.size.estimate", 1024) \
.mode("overwrite") \
.save(path_s3)`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837229546
@jackye1995 Is it necessary to change something to make it work? Because I still have a problem.
My code:
`spark = SparkSession \
.builder \
.appName("data_ingestion_incremental_dev") \
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer") \
.config("spark.sql.hive.convertMetastoreParquet", "false") \
.config("spark.sql.session.timeZone", "America/Sao_Paulo") \
.config("spark.hadoop.mapred.output.committer.class","com.appsflyer.spark.DirectOutputCommitter") \
.config("mapreduce.fileoutputcommitter.marksuccessfuljobs", "false") \
.config("spark.dynamicAllocation.enabled", "true") \
.enableHiveSupport() \
.getOrCreate()`
table.write \
.format("org.apache.hudi") \
.option("hoodie.table.name", f"{data_source}_{table_name}") \
.option("hoodie.datasource.write.table.type", "COPY_ON_WRITE") \
.option("hoodie.datasource.write.operation", "INSERT") \
.option("hoodie.datasource.write.recordkey.field", pk_column) \
.option("hoodie.datasource.write.precombine.field", incremental_column) \
.option("hoodie.datasource.write.keygenerator.class", "org.apache.hudi.keygen.CustomKeyGenerator") \
.option("hoodie.datasource.write.partitionpath.field", "year:SIMPLE, month:SIMPLE, day:SIMPLE") \
.option("hoodie.datasource.hive_sync.enable", True) \
.option("hoodie.datasource.hive_sync.support_timestamp", True) \
.option("hoodie.datasource.hive_sync.database", database_athena) \
.option("hoodie.datasource.hive_sync.table", f"{data_source}_{table_name}") \
.option("hoodie.datasource.hive_sync.partition_fields", "year, month, day") \
.option("hoodie.datasource.hive_sync.partition_extractor_class", "org.apache.hudi.hive.MultiPartKeysValueExtractor") \
.option("hoodie.consistency.check.enabled", True) \
.option("hoodie.parquet.max.file.size", 1073741824) \
.option("hoodie.parquet.small.file.limit", 629145600) \
.option("hoodie.parquet.compression.codec", "snappy") \
.option("hoodie.copyonwrite.insert.split.size", 500000) \
.option("hoodie.copyonwrite.record.size.estimate", 1024) \
.mode("overwrite") \
.save(path_s3)
`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] nsivabalan commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
nsivabalan commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-778324814
Thanks @kimberlyamandalu. Will close this out. Please do reach out to us if you need any more assistance. thanks for helping to better Hudi community :)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837270834
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837229546
@jackye1995 Is it necessary to change something to make it work? Because I still have a problem.
My code:
`spark = SparkSession \`
`.builder \`
`.appName("data_ingestion_incremental_dev") \`
`.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer") \`
`.config("spark.sql.hive.convertMetastoreParquet", "false") \`
`.config("spark.sql.session.timeZone", "America/Sao_Paulo") \`
`.config("spark.hadoop.mapred.output.committer.class","com.appsflyer.spark.DirectOutputCommitter") \`
`.config("mapreduce.fileoutputcommitter.marksuccessfuljobs", "false") \`
`.config("spark.dynamicAllocation.enabled", "true") \`
`.enableHiveSupport() \`
`.getOrCreate()`
`table.write \`
`.format("org.apache.hudi") \`
`.option("hoodie.table.name", f"{data_source}_{table_name}") \`
`.option("hoodie.datasource.write.table.type", "COPY_ON_WRITE") \`
`.option("hoodie.datasource.write.operation", "INSERT") \`
`.option("hoodie.datasource.write.recordkey.field", pk_column) \`
`.option("hoodie.datasource.write.precombine.field", incremental_column) \`
`.option("hoodie.datasource.write.keygenerator.class", "org.apache.hudi.keygen.CustomKeyGenerator") \`
`.option("hoodie.datasource.write.partitionpath.field", "year:SIMPLE, month:SIMPLE, day:SIMPLE") \`
`.option("hoodie.datasource.hive_sync.enable", True) \`
`.option("hoodie.datasource.hive_sync.support_timestamp", True) \`
`.option("hoodie.datasource.hive_sync.database", database_athena) \`
`.option("hoodie.datasource.hive_sync.table", f"{data_source}_{table_name}") \`
`.option("hoodie.datasource.hive_sync.partition_fields", "year, month, day") \`
`.option("hoodie.datasource.hive_sync.partition_extractor_class", "org.apache.hudi.hive.MultiPartKeysValueExtractor") \`
`.option("hoodie.consistency.check.enabled", True) \`
`.option("hoodie.parquet.max.file.size", 1073741824) \`
`.option("hoodie.parquet.small.file.limit", 629145600) \`
`.option("hoodie.parquet.compression.codec", "snappy") \`
`.option("hoodie.copyonwrite.insert.split.size", 500000) \`
`.option("hoodie.copyonwrite.record.size.estimate", 1024) \`
`.mode("overwrite") \`
`.save(path_s3)`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] kimberlyamandalu commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
kimberlyamandalu commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-776974860
@nsivabalan I have not filed a support ticket with Athena. I can do it and will reference ticket in here.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jonathanmorais commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jonathanmorais commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-742082175
@satishkotha does this work for partitioned tables?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] satishkotha edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
satishkotha edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-701154113
This is a bit complicated. Hudi uses spark converters to convert dataframe type into parquet type. Spark SchemaConverters converts timestamp to [int64](https://github.com/apache/spark/blob/master/external/avro/src/main/scala/org/apache/spark/sql/avro/SchemaConverters.scala#L164) with logical type 'TIMESTAMP_MICROS'.
This is because int96 is no longer supported in [parquet](https://issues.apache.org/jira/browse/PARQUET-1883), especially parquet-avro module. In general, int96 is discouraged going forward.
To make timestamp work, we had to
1) Change query engines to support reading parquet logical type. Example for [presto](https://github.com/prestodb/presto/pull/15074/files). We did similar change for Hive. You probably need similar change in Athena
2) Change DLASync/HiveSync to convert logical type TIMESTAMP_MICROS as hive type 'timestamp'. [PR here](https://github.com/apache/hudi/pull/2129)
Unfortunately, there is no clean workaround. As i mentioned, this is a bit complicated. Please don't hesitate to ping me if you have any questions.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] satishkotha commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
satishkotha commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-768113848
@jonathanmorais Were you able to figure out the problem? Can you share more details on behavior you see with this flag enabled? Is the timestamp field being registered as some other type?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837241384
For example, the output of Dataframe .show is: `2017-05-09 08:21:35`, but output at Athena is `+49134-01-07 05:30:00.000`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] satishkotha edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
satishkotha edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-736866170
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] nsivabalan commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
nsivabalan commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-776821258
@kimberlyamandalu : were you able to file a support ticket w/ athena? Once done, can you reference a link here.
CC @umehrot2 : fyi, timestamp datatype conversion in hudi to athena has problems. As per satish, we might need a fix in athena. You can read this entire ticket for reference.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] kimberlyamandalu commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
kimberlyamandalu commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-777044719
I have raised Case# 7979845961 with AWS support for this.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] kimberlyamandalu commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
kimberlyamandalu commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-736932283
@satishkotha Thanks for your response. I understand. And just to confirm, the Presto version in Athena is 0.217.
Which version is the fix in? Thanks!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] nsivabalan commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
nsivabalan commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-774520484
@jonathanmorais : few quick questions as we triage the issue.
- Were you running older version of Hudi and encountered this trying to upgrade to a latest version?
- Is this affecting your production? trying to gauge the severity.
- Or you are trying out a POC ? and this is the first time trying out Hudi.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] n3nash commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
n3nash commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-700966424
@satishkotha can you please help here ? This seems like the same issue we encountered.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] nsivabalan closed issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
nsivabalan closed issue #2123:
URL: https://github.com/apache/hudi/issues/2123
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jonathanmorais edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jonathanmorais edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-742607488
@satishkotha not exacly, I followed the tip `hoodie.datasource.hive_sync.support_timestamp=true` and it's not working for me, using partitioned tables, I thought this could be the problem.
here my spark-submit:
```
spark-submit --class org.apache.hudi.utilities.deltastreamer.HoodieDeltaStreamer \
--packages org.apache.spark:spark-avro_2.11:2.4.4,org.apache.hudi:hudi-utilities-bundle_2.11:0.5.1-incubating \
--conf "spark.serializer=org.apache.spark.serializer.KryoSerializer" \
--master yarn --deploy-mode client \
/usr/lib/hudi/hudi-spark-bundle.jar,/usr/lib/hudi/hudi-utilities-bundle.jar,/usr/lib/spark/external/lib/spark-avro.jar \
--source-ordering-field <if_field> \
--source-class org.apache.hudi.utilities.sources.ParquetDFSSource \
--target-base-path s3://<bucket>/<prefix --target-table <table> --table-type COPY_ON_WRITE \
--transformer-class org.apache.hudi.utilities.transform.AWSDmsTransformer \
--payload-class org.apache.hudi.payload.AWSDmsAvroPayload \
--hoodie-conf hoodie.datasource.hive_sync.support_timestamp=true,hoodie.datasource.write.recordkey.field=<field_id>,hoodie.datasource.write.partitionpath.field=<partition_field>,hoodie.deltastreamer.source.dfs.root=s3://<bucket>/<preifx>
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] nsivabalan commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
nsivabalan commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-779369814
@kimberlyamandalu : great. thanks a bunch. We will try to add it to our faq.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] kimberlyamandalu commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
kimberlyamandalu commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-778464849
For reference, this is their response for the ticket.
They have suggested a workaround.
Greetings Kim,
Thank you for contacting AWS Premium Support. This is Mrunmayee and I will be assisting you with the case.
I did some investigation on the timestamps not being displayed correctly in Athena. As pointed out by you, this is caused When a Glue crawler is run on the parquet files, the field is confirmed as TIMESTAMP as granularity of timestamp file fields is in "TIMESTAMP_MICROS".
In the Presto documentation [1], it is given that timestamp granularity up to millisecond is supported but not microseconds. As Athena uses Presto engine as the backend query engine, this limitation of Presto validates for Athena also which is why we are not being able to see the correct timestamp for those columns in Athena console.
In order to deal with this issue, one possible workaround is to change the data type of timestamp columns from "timestamp" to "bigint" by editing the schema of your table “table_name”. Here “createdt” is the column of concern
You can do this by following the below mentioned steps:
1. Go to the Glue console.
2. Navigate to the "Tables" option on the sidebar on the left hand side of the console.
3. Select your table, “table_name”
4. On the right hand side of the console, select the "Edit Schemas" option.
5. For the field "createdt", select on the datatype (which is currently set to timestamp) and change it to bigint by selecting on it.
6. Click on "Save" to save the changes.
Then we can create a view over the table using Presto functions eg. "from_unixtime" [2].
For example : I have changed the 'createdt' data type as bigint and ran the following query to get the the desired result with correct timestamp value :
------------
SELECT id, code, from_unixtime(createdt/1000000) AS createdt FROM "default”.”table”_name limit 10;
------------
Let me know if this workaround works for you. As far as a patch/fix is concerned, at the moment, I do not have visibility into it. That being said, I will look into this and provide you with an update as soon as I have one. Apologies for the inconvenience cause.
I hope you find this information useful. Apologies in advance if I misunderstood your case. Please feel free to get in touch with me if you have any questions or need clarifications with anything.
References :
[1] TIMESTAMP limitations - http://teradata.github.io/presto/docs/127t/release/unsupported.html
[2] from_unixtime() - https://prestodb.github.io/docs/current/functions/datetime.html
We value your feedback. Please share your experience by rating this correspondence using the AWS Support Center link at the end of this correspondence. Each correspondence can also be rated by selecting the stars in top right corner of each correspondence within the AWS Support Center.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jonathanmorais commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jonathanmorais commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-742607488
@satishkotha not exacly, I followed the tip `hoodie.datasource.hive_sync.support_timestamp=true` and it's not working for me, using partitioned tables, I thought this could be the problem.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon removed a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon removed a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837241384
For example, the output of Dataframe .show is: `2017-05-09 08:21:35`, but output at Athena is `+49134-01-07 05:30:00.000`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] kimberlyamandalu commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
kimberlyamandalu commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-736849418
Hello,
I am also encountering this issue when querying Hudi dataset with Athena.
Is there anythingI can do to get it to show correctly? Sorry if the question is a repeat but the workaround is not very clear to me.
Thanks.
Kim
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837242269
> @l-jhon could you provide more details on what your problem is? What is the table you create in Athena/Glue, and what query did you run?
For example, the output of Dataframe .show is: 2017-05-09 08:21:35, but output at Athena is +49134-01-07 05:30:00.000
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jackye1995 edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jackye1995 edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837234410
@l-jhon could you provide more details on what your problem is? What is the table you create in Athena/Glue, and what query did you run?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] nsivabalan commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
nsivabalan commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-766439219
@satishkotha : when you get a chance, can you please follow up on this.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jackye1995 commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jackye1995 commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837249804
> For example, the output of Dataframe .show is: 2017-05-09 08:21:35, but output at Athena is +49134-01-07 05:30:00.000
What is the schema of the table through `DESCRIBE table`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837229546
@jackye1995 Is it necessary to change something to make it work? Because I still have a problem.
My code:
`spark = SparkSession \
.builder \
.appName("data_ingestion_incremental_dev") \
.config("spark.serializer", "org.apache.spark.serializer.KryoSerializer") \
.config("spark.sql.hive.convertMetastoreParquet", "false") \
.config("spark.sql.session.timeZone", "America/Sao_Paulo") \
.config("spark.hadoop.mapred.output.committer.class","com.appsflyer.spark.DirectOutputCommitter") \
.config("mapreduce.fileoutputcommitter.marksuccessfuljobs", "false") \
.config("spark.dynamicAllocation.enabled", "true") \
.enableHiveSupport() \
.getOrCreate()
table.write \
.format("org.apache.hudi") \
.option("hoodie.table.name", f"{data_source}_{table_name}") \
.option("hoodie.datasource.write.table.type", "COPY_ON_WRITE") \
.option("hoodie.datasource.write.operation", "INSERT") \
.option("hoodie.datasource.write.recordkey.field", pk_column) \
.option("hoodie.datasource.write.precombine.field", incremental_column) \
.option("hoodie.datasource.write.keygenerator.class", "org.apache.hudi.keygen.CustomKeyGenerator") \
.option("hoodie.datasource.write.partitionpath.field", "year:SIMPLE, month:SIMPLE, day:SIMPLE") \
.option("hoodie.datasource.hive_sync.enable", True) \
.option("hoodie.datasource.hive_sync.support_timestamp", True) \
.option("hoodie.datasource.hive_sync.database", database_athena) \
.option("hoodie.datasource.hive_sync.table", f"{data_source}_{table_name}") \
.option("hoodie.datasource.hive_sync.partition_fields", "year, month, day") \
.option("hoodie.datasource.hive_sync.partition_extractor_class", "org.apache.hudi.hive.MultiPartKeysValueExtractor") \
.option("hoodie.consistency.check.enabled", True) \
.option("hoodie.parquet.max.file.size", 1073741824) \
.option("hoodie.parquet.small.file.limit", 629145600) \
.option("hoodie.parquet.compression.codec", "snappy") \
.option("hoodie.copyonwrite.insert.split.size", 500000) \
.option("hoodie.copyonwrite.record.size.estimate", 1024) \
.mode("overwrite") \
.save(path_s3)`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] satishkotha commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
satishkotha commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-742087907
@jonathanmorais Yes, Both partitioned and non-partitioned tables are working for us with above steps. Are you seeing any problem for partitioned tables?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] l-jhon commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
l-jhon commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837270834
> > For example, the output of Dataframe .show is: 2017-05-09 08:21:35, but output at Athena is +49134-01-07 05:30:00.000
>
> What is the schema of the table through `DESCRIBE table`?
Follows describe:
_hoodie_commit_time string
_hoodie_commit_seqno string
_hoodie_record_key string
_hoodie_partition_path string
_hoodie_file_name string
origin string
created_at timestamp
updated_at timestamp
id string
channel_order_code string
purchase_timestamp timestamp
# Partition Information
year string
month string
day string
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jackye1995 commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jackye1995 commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-825205939
Just FYI, a similar fix has been done after that ticket was created, and this issue should no longer exist in Athena. You can now use timestamp as the column type and there is no need to perform a conversion using something like `from_unixtime(createdt/1000000)`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jackye1995 commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jackye1995 commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-837234410
@l-jhon could you provide more details on what your problem is? What is the table you create in Athena/Glue, and what query you ran?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] satishkotha commented on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
satishkotha commented on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-736866170
@kimberlyamandalu This requires a change in Athena service. You can contact their support to apply patch similar to [presto](https://github.com/prestodb/presto/pull/15074/files))?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] jonathanmorais edited a comment on issue #2123: Timestamp not parsed correctly on Athena
Posted by GitBox <gi...@apache.org>.
jonathanmorais edited a comment on issue #2123:
URL: https://github.com/apache/hudi/issues/2123#issuecomment-742607488
@satishkotha not exacly, I followed the tip `hoodie.datasource.hive_sync.support_timestamp=true` and it's not working for me, using partitioned tables, I thought this could be the problem.
here my spark-submit:
```
spark-submit --class org.apache.hudi.utilities.deltastreamer.HoodieDeltaStreamer \
--packages org.apache.spark:spark-avro_2.11:2.4.4,org.apache.hudi:hudi-utilities-bundle_2.11:0.5.1-incubating \
--conf "spark.serializer=org.apache.spark.serializer.KryoSerializer" \
--master yarn --deploy-mode client \
/usr/lib/hudi/hudi-spark-bundle.jar,/usr/lib/hudi/hudi-utilities-bundle.jar,/usr/lib/spark/external/lib/spark-avro.jar \
--source-ordering-field <id_field> \
--source-class org.apache.hudi.utilities.sources.ParquetDFSSource \
--target-base-path s3://<bucket>/<prefix --target-table <table> --table-type COPY_ON_WRITE \
--transformer-class org.apache.hudi.utilities.transform.AWSDmsTransformer \
--payload-class org.apache.hudi.payload.AWSDmsAvroPayload \
--hoodie-conf hoodie.datasource.hive_sync.support_timestamp=true,hoodie.datasource.write.recordkey.field=<id_field>,hoodie.datasource.write.partitionpath.field=<partition_field>,hoodie.deltastreamer.source.dfs.root=s3://<bucket>/<preifx>
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org