CODEWITHSUNDEEP
sqlhiveprestolast-modifiedtrino
yogeshiitm
yogeshiitm

Reputation: 309

How to find last_updated time of a hive table using presto query?

I am using trino-python-client to query the hive database https://github.com/trinodb/trino-python-client:

from trino.dbapi import connect

conn = connect(
    host="<host>",
    port=<port>,
    user="<username>",
    catalog="hive",
    schema="default",
)
cur = conn.cursor()
cur.execute("SELECT * FROM <schema_name>.<table_name>")
rows = cur.fetchall()

Now I want to fetch the last_updated time of a hive table using trino connection. So I tried running the following queries, but none of them worked.

  1. describe formatted <table_name> Source: How can I find last modified timestamp for a table in Hive?

Query failed: mismatched input '<table_name>'. Expecting: '.', <EOF>

  1. SELECT transient_lastddltime FROM "<table_name>$properties" Source: How to find when record was last updated?

Query failed: Table '<table_name>$properties' does not exist

  1. show TBLPROPERTIES <table_name> ('transient_lastDdlTime'); Source: https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-aux-show-tblproperties.html

Query failed: mismatched input 'TBLPROPERTIES'. Expecting: 'CATALOGS', 'COLUMNS', 'CREATE', 'CURRENT', 'FUNCTIONS', 'GRANTS', 'ROLE', 'ROLES', 'SCHEMAS', 'SESSION', 'STATS', 'TABLES'

Though running the hive queries using spark SQL, gave the desired data. Is it even possible to fetch metadata using presto connection? If yes, please help me with it.

Upvotes: 0

Views: 2562

Answers (1)

MonteCarloSims
MonteCarloSims

Reputation: 1771

You can use the $file_modified_time special column

SELECT *, "$file_modified_time"
FROM hive.foo.table;

Other special columns include:

$bucket
$path
$file_size
$partition

Upvotes: 1

Related Questions