AWS Athena System and DDL Queries
The information below contains examples of common AWS Athena system queries and DDL statements. The structure of the Athena database starts with a top-level catalog named the AWSDataCatalog. Under the AWSDataCatalog, Athena contains objects it calls databases. The databases are similar to the term schemas in other database systems.
To show all databases in the Athena AWSDataCatalog for an Athena instance, execute the following query:
show databases
The output of the show databases command looks like the following:
database_name
_____________
sales
marketing
hr
To show all of the tables in a particular database / schema, the following query can be executed:
show tables in sales
The output of the show tables in database command looks like the following:
tab_name
____________
sales_reps
accounts
contacts
To show all views in a particular database / schema, the following command can be executed:
show views in sales
The output of the show views command looks like the following:
views
_____________
contacts_view
account_view
To show the columns in a particular table, execute the following command:
show columns in sales.accounts
The output of the show columns command looks like the following:
field
___________
id
firstName
lastName
address
To show the create table DDL statement for a particular table, the show create table command can be executed:
show create table sales.accounts
The output of the show create table is the following:
createtab_stmt
____________________________________
CREATE EXTERNAL TABLE `sales.accounts`(
`id` int,
`firstName` string,
`lastName` string,
`address` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://bucket_location'
TBLPROPERTIES (
'transient_lastDdlTime'='1481299789')
