def get_timestamp_column_name(psql, db_name, table):
"""
auto detect timestamp column name that is also a sort key.
Args:
psql -- handle to talk to redshift
db -- redshift database containing table
table -- table name
Return: column name
Throw: ValueError
"""
rs_schema = get_redshift_schema()
params = {
'tablename': table,
'schemaname': rs_schema,
}
result = psql.run_sql(
QUERY_GET_SORT_COLUMN,
db_name,
"find sort column",
params=params,
output=True,
schema=rs_schema
)
column = get_sortkey_column(result, 'timestamp')
if len(column) == 0:
column = get_sortkey_column(result, 'date')
if len(column) == 0:
return None
if len(column) > 1:
raise ValueError("too many sort columns in {0}".format(table))
return column.pop()
def get_current_tables(rs_psql, database):
"""
get_current_tables gets a list of current tables
Args:
rs_psql -- the RedshiftPostgres object used to run the SQL command
database -- the redshift db name to which we copy the table
Returns:
a list of table names
"""
schemaname = get_redshift_schema()
SELECT = "SELECT tablename \
FROM pg_catalog.pg_tables \
where schemaname=%(schemaname)s"
params = {'schemaname': schemaname}
tables = rs_psql.run_sql(
SELECT,
database,
'getting existing tables',
output=True,
params=params
)
return [tbl_name for (tbl_name,) in tables]
def get_timestamp_column_name(psql, db_name, table):
"""
auto detect timestamp column name that is also a sort key.
Args:
psql -- handle to talk to redshift
db -- redshift database containing table
table -- table name
Return: column name
Throw: ValueError
"""
rs_schema = get_redshift_schema()
params = {
'tablename': table,
'schemaname': rs_schema,
}
result = psql.run_sql(QUERY_GET_SORT_COLUMN,
db_name,
"find sort column",
params=params,
output=True,
schema=rs_schema)
column = get_sortkey_column(result, 'timestamp')
if len(column) == 0:
column = get_sortkey_column(result, 'date')
if len(column) == 0:
return None
if len(column) > 1:
raise ValueError("too many sort columns in {0}".format(table))
return column.pop()
def get_current_tables(rs_psql, database):
"""
get_current_tables gets a list of current tables
Args:
rs_psql -- the RedshiftPostgres object used to run the SQL command
database -- the redshift db name to which we copy the table
Returns:
a list of table names
"""
schemaname = get_redshift_schema()
SELECT = "SELECT tablename \
FROM pg_catalog.pg_tables \
where schemaname=%(schemaname)s"
params = {'schemaname': schemaname}
tables = rs_psql.run_sql(SELECT,
database,
'getting existing tables',
output=True,
params=params)
return [tbl_name for (tbl_name, ) in tables]
def get_table_def(psql, db, tablename):
""" Retrieve table definition stored in the database.
Table definitions are in pg_table_def, see QUERY_TABLE_DEF for details
tablename -- table name for which to get definition
Returns: A list of tuples. Each entry in the list is for a table column,
Each tuple describes column attributes such as name, encoding, etc.
NOTE: For tables not in the database, get_table_def returns empty list
"""
rs_schema = get_redshift_schema()
param_dict = {
'tablename': tablename,
'schemaname': rs_schema,
}
results = psql.run_sql(QUERY_TABLE_DEF,
db,
"getting table def",
params=param_dict,
output=True,
schema=rs_schema)
return results
def get_table_def(psql, db, tablename):
""" Retrieve table definition stored in the database.
Table definitions are in pg_table_def, see QUERY_TABLE_DEF for details
tablename -- table name for which to get definition
Returns: A list of tuples. Each entry in the list is for a table column,
Each tuple describes column attributes such as name, encoding, etc.
NOTE: For tables not in the database, get_table_def returns empty list
"""
rs_schema = get_redshift_schema()
param_dict = {
'tablename': tablename,
'schemaname': rs_schema,
}
results = psql.run_sql(
QUERY_TABLE_DEF,
db,
"getting table def",
params=param_dict,
output=True,
schema=rs_schema
)
return results
