import pydbtools as pydb
pydb.create_temp_table(sql, table_name)
The above function would wrap their sql into:
f"""
CREATE TABLE user_name.{table_name} AS
WITH properties (
s3_location, <Same location as where we dump user tables>
format='parquet'
)
{sql}
"""
Thoughts on the above:
Database name collisions.
When this package runs a query it uses boto3 sts client to get the iam role. That should probably be used to create the database username. This would easily avoid database collisions.
With the above would you still expect users to specify the database name everytime they want to reference a temporary table? If so you could create your own string naming like FROM #temp.table1 where pydbtools replaces #temp with the actual database name.
You should add context management to this package so that when you exit pydbtools will delete the data and the database from the glue catalogue.
We are going to need a retension policy on temp tables in the glue catalogue. This is interesting (but defo solvable), should add this as an issue to trello once this functionality is added.
We should add this functionality:
The above function would wrap their sql into:
Thoughts on the above:
FROM #temp.table1
where pydbtools replaces#temp
with the actual database name.