Handy Tricks to explore DB/Athena

Python-read-data.py

-- Search for a columns in DB
SELECT      COLUMN_NAME AS "ColumnName",
            TABLE_NAME AS  "TableName",
            TABLE_SCHEMA
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE 'cashfac_id'
ORDER BY    TABLE_NAME,
            COLUMN_NAME;

-- List all columns one table
SELECT      COLUMN_NAME AS "ColumnName",
            TABLE_NAME AS  "TableName",
            TABLE_SCHEMA
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE 'cashfac_id'
ORDER BY    TABLE_NAME,
            COLUMN_NAME;
            
-- Explore Athena
-- Query to find all the columns in athena, which are having 'transactions' within its name */

SELECT table_catalog as data_catalog, table_schema as tablename, table_name as columnname
FROM information_schema.tables
WHERE information_schema.tables.table_name like '%transactions%' 
order by table_schema, table_name;

/* Note: AWS Athena specific syntax:
         data catalog : table_catalog
         table name   : table_schema
         column name  : table_name
*/