This documentation is for Version 1 of the Amazon CLI only. For documentation related to Version 2 of the Amazon CLI, see the Version 2 User Guide.
Athena examples using Amazon CLI
The following code examples show you how to perform actions and implement common scenarios by using the Amazon Command Line Interface with Athena.
Actions are code excerpts from larger programs and must be run in context. While actions show you how to call individual service functions, you can see actions in context in their related scenarios.
Each example includes a link to the complete source code, where you can find instructions on how to set up and run the code in context.
Topics
Actions
The following code example shows how to use batch-get-named-query
.
- Amazon CLI
-
To return information about more than one query
The following
batch-get-named-query
example returns information about the named queries that have the specified IDs.aws athena batch-get-named-query \ --named-query-ids
a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
a1b2c3d4-5678-90ab-cdef-EXAMPLE22222
a1b2c3d4-5678-90ab-cdef-EXAMPLE33333
Output:
{ "NamedQueries": [ { "Name": "Flights Select Query", "Description": "Sample query to get the top 10 airports with the most number of departures since 2000", "Database": "sampledb", "QueryString": "SELECT origin, count(*) AS total_departures\nFROM\nflights_parquet\nWHERE year >= '2000'\nGROUP BY origin\nORDER BY total_departures DESC\nLIMIT 10;", "NamedQueryId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111", "WorkGroup": "primary" }, { "Name": "Load flights table partitions", "Description": "Sample query to load flights table partitions using MSCK REPAIR TABLE statement", "Database": "sampledb", "QueryString": "MSCK REPAIR TABLE flights_parquet;", "NamedQueryId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE22222", "WorkGroup": "primary" }, { "Name": "CloudFront Select Query", "Description": "Sample query to view requests per operating system during a particular time frame", "Database": "sampledb", "QueryString": "SELECT os, COUNT(*) count FROM cloudfront_logs WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05' GROUP BY os;", "NamedQueryId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE33333", "WorkGroup": "primary" } ], "UnprocessedNamedQueryIds": [] }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see BatchGetNamedQuery
in Amazon CLI Command Reference.
-
The following code example shows how to use batch-get-query-execution
.
- Amazon CLI
-
To return information about one or more query executions
The following
batch-get-query-execution
example returns query execution information for the queries that have the specified query IDs.aws athena batch-get-query-execution \ --query-execution-ids
a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
a1b2c3d4-5678-90ab-cdef-EXAMPLE22222
Output:
{ "QueryExecutions": [ { "QueryExecutionId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111", "Query": "create database if not exists webdata", "StatementType": "DDL", "ResultConfiguration": { "OutputLocation": "s3://awsdoc-example-bucket/a1b2c3d4-5678-90ab-cdef-EXAMPLE11111.txt" }, "QueryExecutionContext": {}, "Status": { "State": "SUCCEEDED", "SubmissionDateTime": 1593470720.592, "CompletionDateTime": 1593470720.902 }, "Statistics": { "EngineExecutionTimeInMillis": 232, "DataScannedInBytes": 0, "TotalExecutionTimeInMillis": 310, "ResultConfiguration": { "QueryQueueTimeInMillis": 50, "ServiceProcessingTimeInMillis": 28 }, "WorkGroup": "AthenaAdmin" }, { "QueryExecutionId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE22222", "Query": "select date, location, browser, uri, status from cloudfront_logs where method = 'GET' and status = 200 and location like 'SFO%' limit 10", "StatementType": "DML", "ResultConfiguration": { "OutputLocation": "s3://awsdoc-example-bucket/a1b2c3d4-5678-90ab-cdef-EXAMPLE22222.csv" }, "QueryExecutionContext": { "Database": "mydatabase", "Catalog": "awsdatacatalog" }, "Status": { "State": "SUCCEEDED", "SubmissionDateTime": 1593469842.665, "CompletionDateTime": 1593469846.486 }, "Statistics": { "EngineExecutionTimeInMillis": 3600, "DataScannedInBytes": 203089, "TotalExecutionTimeInMillis": 3821, "QueryQueueTimeInMillis": 267, "QueryPlanningTimeInMillis": 1175 }, "WorkGroup": "AthenaAdmin" } ], "UnprocessedQueryExecutionIds": [] }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see BatchGetQueryExecution
in Amazon CLI Command Reference.
-
The following code example shows how to use create-data-catalog
.
- Amazon CLI
-
To create a data catalog
The following
create-data-catalog
example creates thedynamo_db_catalog
data catalog.aws athena create-data-catalog \ --name
dynamo_db_catalog
\ --typeLAMBDA
\ --description"DynamoDB Catalog"
\ --parametersfunction=arn:aws:lambda:us-west-2:111122223333:function:dynamo_db_lambda
This command produces no output. To see the result, use
aws athena get-data-catalog --name dynamo_db_catalog
.For more information, see Registering a Catalog: create-data-catalog
in the Amazon Athena User Guide. -
For API details, see CreateDataCatalog
in Amazon CLI Command Reference.
-
The following code example shows how to use create-named-query
.
- Amazon CLI
-
To create a named query
The following
create-named-query
example creates a saved query in theAthenaAdmin
workgroup that queries theflights_parquet
table for flights from Seattle to New York in January, 2016 whose departure and arrival were both delayed by more than ten minutes. Because the airport code values in the table are strings that include double quotes (for example, "SEA"), they are escaped by backslashes and surrounded by single quotes.aws athena create-named-query \ --name
"SEA to JFK delayed flights Jan 2016"
\ --description"Both arrival and departure delayed more than 10 minutes."
\ --databasesampledb
\ --query-string "SELECT flightdate, carrier, flightnum, origin, dest, depdelayminutes, arrdelayminutes FROM sampledb.flights_parquet WHERE yr = 2016 AND month = 1 AND origin = '\"SEA\"' AND dest = '\"JFK\"' AND depdelayminutes > 10 AND arrdelayminutes > 10" \ --work-groupAthenaAdmin
Output:
{ "NamedQueryId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111" }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see CreateNamedQuery
in Amazon CLI Command Reference.
-
The following code example shows how to use create-work-group
.
- Amazon CLI
-
To create a workgroup
The following
create-work-group
example creates a workgroup calledData_Analyst_Group
that has the query results output locations3://awsdoc-example-bucket
. The command creates a workgroup that overrides client configuration settings, which includes the query results output location. The command also enables CloudWatch metrics and adds three key-value tag pairs to the workgroup to distinguish it from other workgroups. Note that the--configuration
argument has no spaces before the commas that separate its options.aws athena create-work-group \ --name
Data_Analyst_Group
\ --configuration ResultConfiguration={OutputLocation="s3://awsdoc-example-bucket"},EnforceWorkGroupConfiguration="true",PublishCloudWatchMetricsEnabled="true" \ --description"Workgroup for data analysts"
\ --tagsKey=Division,Value=West
Key=Location,Value=Seattle
Key=Team,Value="Big Data"This command produces no output. To see the results, use
aws athena get-work-group --work-group Data_Analyst_Group
.For more information, see Managing Workgroups
in the Amazon Athena User Guide. -
For API details, see CreateWorkGroup
in Amazon CLI Command Reference.
-
The following code example shows how to use delete-data-catalog
.
- Amazon CLI
-
To delete a data catalog
The following
delete-data-catalog
example deletes theUnusedDataCatalog
data catalog.aws athena delete-data-catalog \ --name
UnusedDataCatalog
This command produces no output.
For more information, see Deleting a Catalog: delete-data-catalog
in the Amazon Athena User Guide. -
For API details, see DeleteDataCatalog
in Amazon CLI Command Reference.
-
The following code example shows how to use delete-named-query
.
- Amazon CLI
-
To delete a named query
The following
delete-named-query
example deletes the named query that has the specified ID.aws athena delete-named-query \ --named-query-id
a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
This command produces no output.
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see DeleteNamedQuery
in Amazon CLI Command Reference.
-
The following code example shows how to use delete-work-group
.
- Amazon CLI
-
To delete a workgroup
The following
delete-work-group
example deletes theTeamB
workgroup.aws athena delete-work-group \ --work-group
TeamB
This command produces no output. To confirm the deletion, use
aws athena list-work-groups
.For more information, see Managing Workgroups
in the Amazon Athena User Guide. -
For API details, see DeleteWorkGroup
in Amazon CLI Command Reference.
-
The following code example shows how to use get-data-catalog
.
- Amazon CLI
-
To return information about a data catalog
The following
get-data-catalog
example returns information about thedynamo_db_catalog
data catalog.aws athena get-data-catalog \ --name
dynamo_db_catalog
Output:
{ "DataCatalog": { "Name": "dynamo_db_catalog", "Description": "DynamoDB Catalog", "Type": "LAMBDA", "Parameters": { "catalog": "dynamo_db_catalog", "metadata-function": "arn:aws:lambda:us-west-2:111122223333:function:dynamo_db_lambda", "record-function": "arn:aws:lambda:us-west-2:111122223333:function:dynamo_db_lambda" } } }
For more information, see Showing Catalog Details: get-data-catalog
in the Amazon Athena User Guide. -
For API details, see GetDataCatalog
in Amazon CLI Command Reference.
-
The following code example shows how to use get-database
.
- Amazon CLI
-
To return information about a database in a data catalog
The following
get-database
example returns information about thesampledb
database in theAwsDataCatalog
data catalog.aws athena get-database \ --catalog-name
AwsDataCatalog
\ --database-namesampledb
Output:
{ "Database": { "Name": "sampledb", "Description": "Sample database", "Parameters": { "CreatedBy": "Athena", "EXTERNAL": "TRUE" } } }
For more information, see Showing Database Details: get-database
in the Amazon Athena User Guide. -
For API details, see GetDatabase
in Amazon CLI Command Reference.
-
The following code example shows how to use get-named-query
.
- Amazon CLI
-
To return a named query
The following
get-named-query
example returns information about the query that has the specified ID.aws athena get-named-query \ --named-query-id
a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
Output:
{ "NamedQuery": { "Name": "CloudFront Logs - SFO", "Description": "Shows successful GET request data for SFO", "Database": "default", "QueryString": "select date, location, browser, uri, status from cloudfront_logs where method = 'GET' and status = 200 and location like 'SFO%' limit 10", "NamedQueryId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111", "WorkGroup": "AthenaAdmin" } }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see GetNamedQuery
in Amazon CLI Command Reference.
-
The following code example shows how to use get-query-execution
.
- Amazon CLI
-
To return information about a query execution
The following
get-query-execution
example returns information about the query that has the specified query ID.aws athena get-query-execution \ --query-execution-id
a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
Output:
{ "QueryExecution": { "QueryExecutionId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111", "Query": "select date, location, browser, uri, status from cloudfront_logs where method = 'GET ' and status = 200 and location like 'SFO%' limit 10", "StatementType": "DML", "ResultConfiguration": { "OutputLocation": "s3://awsdoc-example-bucket/a1b2c3d4-5678-90ab-cdef-EXAMPLE11111.csv" }, "QueryExecutionContext": { "Database": "mydatabase", "Catalog": "awsdatacatalog" }, "Status": { "State": "SUCCEEDED", "SubmissionDateTime": 1593469842.665, "CompletionDateTime": 1593469846.486 }, "Statistics": { "EngineExecutionTimeInMillis": 3600, "DataScannedInBytes": 203089, "TotalExecutionTimeInMillis": 3821, "QueryQueueTimeInMillis": 267, "QueryPlanningTimeInMillis": 1175 }, "WorkGroup": "AthenaAdmin" } }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see GetQueryExecution
in Amazon CLI Command Reference.
-
The following code example shows how to use get-query-results
.
- Amazon CLI
-
To return the results of a query
The following
get-query-results
example returns the results of the query that has the specified query ID.aws athena get-query-results \ --query-execution-id
a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
Output:
{ "ResultSet": { "Rows": [ { "Data": [ { "VarCharValue": "date" }, { "VarCharValue": "location" }, { "VarCharValue": "browser" }, { "VarCharValue": "uri" }, { "VarCharValue": "status" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Safari" }, { "VarCharValue": "/test-image-2.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Opera" }, { "VarCharValue": "/test-image-2.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Firefox" }, { "VarCharValue": "/test-image-3.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Lynx" }, { "VarCharValue": "/test-image-3.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "IE" }, { "VarCharValue": "/test-image-2.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Opera" }, { "VarCharValue": "/test-image-1.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Chrome" }, { "VarCharValue": "/test-image-3.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Firefox" }, { "VarCharValue": "/test-image-2.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "Chrome" }, { "VarCharValue": "/test-image-3.jpeg" }, { "VarCharValue": "200" } ] }, { "Data": [ { "VarCharValue": "2014-07-05" }, { "VarCharValue": "SFO4" }, { "VarCharValue": "IE" }, { "VarCharValue": "/test-image-2.jpeg" }, { "VarCharValue": "200" } ] } ], "ResultSetMetadata": { "ColumnInfo": [ { "CatalogName": "hive", "SchemaName": "", "TableName": "", "Name": "date", "Label": "date", "Type": "date", "Precision": 0, "Scale": 0, "Nullable": "UNKNOWN", "CaseSensitive": false }, { "CatalogName": "hive", "SchemaName": "", "TableName": "", "Name": "location", "Label": "location", "Type": "varchar", "Precision": 2147483647, "Data": [ "Scale": 0, "Nullable": "UNKNOWN", "CaseSensitive": true }, { "CatalogName": "hive", "SchemaName": "", "TableName": "", "Name": "browser", "Label": "browser", "Type": "varchar", "Precision": 2147483647, "Scale": 0, "Nullable": "UNKNOWN", "CaseSensitive": true }, { "CatalogName": "hive", "SchemaName": "", "TableName": "", "Name": "uri", "Label": "uri", "Type": "varchar", "Precision": 2147483647, "Scale": 0, "Nullable": "UNKNOWN", "CaseSensitive": true }, { "CatalogName": "hive", "SchemaName": "", "TableName": "", "Name": "status", "Label": "status", "Type": "integer", "Precision": 10, "Scale": 0, "Nullable": "UNKNOWN", "CaseSensitive": false } ] } }, "UpdateCount": 0 }
For more information, see Working with Query Results, Output Files, and Query History
in the Amazon Athena User Guide. -
For API details, see GetQueryResults
in Amazon CLI Command Reference.
-
The following code example shows how to use get-table-metadata
.
- Amazon CLI
-
To return metadata information about a table
The following
get-table-metadata
example returns metadata information about thecounties
table, including including column names and their datatypes, from thesampledb
database of theAwsDataCatalog
data catalog.aws athena get-table-metadata \ --catalog-name
AwsDataCatalog
\ --database-namesampledb
\ --table-namecounties
Output:
{ "TableMetadata": { "Name": "counties", "CreateTime": 1593559968.0, "LastAccessTime": 0.0, "TableType": "EXTERNAL_TABLE", "Columns": [ { "Name": "name", "Type": "string", "Comment": "from deserializer" }, { "Name": "boundaryshape", "Type": "binary", "Comment": "from deserializer" }, { "Name": "motto", "Type": "string", "Comment": "from deserializer" }, { "Name": "population", "Type": "int", "Comment": "from deserializer" } ], "PartitionKeys": [], "Parameters": { "EXTERNAL": "TRUE", "inputformat": "com.esri.json.hadoop.EnclosedJsonInputFormat", "location": "s3://awsdoc-example-bucket/json", "outputformat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat", "serde.param.serialization.format": "1", "serde.serialization.lib": "com.esri.hadoop.hive.serde.JsonSerde", "transient_lastDdlTime": "1593559968" } } }
For more information, see Showing Table Details: get-table-metadata
in the Amazon Athena User Guide. -
For API details, see GetTableMetadata
in Amazon CLI Command Reference.
-
The following code example shows how to use get-work-group
.
- Amazon CLI
-
To return information about a workgroup
The following
get-work-group
example returns information about theAthenaAdmin
workgroup.aws athena get-work-group \ --work-group
AthenaAdmin
Output:
{ "WorkGroup": { "Name": "AthenaAdmin", "State": "ENABLED", "Configuration": { "ResultConfiguration": { "OutputLocation": "s3://awsdoc-example-bucket/" }, "EnforceWorkGroupConfiguration": false, "PublishCloudWatchMetricsEnabled": true, "RequesterPaysEnabled": false }, "Description": "Workgroup for Athena administrators", "CreationTime": 1573677174.105 } }
For more information, see Managing Workgroups
in the Amazon Athena User Guide. -
For API details, see GetWorkGroup
in Amazon CLI Command Reference.
-
The following code example shows how to use list-data-catalogs
.
- Amazon CLI
-
To list the data catalogs registered with Athena
The following
list-data-catalogs
example lists the data catalogs registered with Athena.aws athena list-data-catalogs
Output:
{ "DataCatalogsSummary": [ { "CatalogName": "AwsDataCatalog", "Type": "GLUE" }, { "CatalogName": "cw_logs_catalog", "Type": "LAMBDA" }, { "CatalogName": "cw_metrics_catalog", "Type": "LAMBDA" } ] }
For more information, see Listing Registered Catalogs: list-data-catalogs
in the Amazon Athena User Guide. -
For API details, see ListDataCatalogs
in Amazon CLI Command Reference.
-
The following code example shows how to use list-databases
.
- Amazon CLI
-
To list the databases in a data catalog
The following
list-databases
example lists the databases in theAwsDataCatalog
data catalog.aws athena list-databases \ --catalog-name
AwsDataCatalog
Output:
{ "DatabaseList": [ { "Name": "default" }, { "Name": "mydatabase" }, { "Name": "newdb" }, { "Name": "sampledb", "Description": "Sample database", "Parameters": { "CreatedBy": "Athena", "EXTERNAL": "TRUE" } }, { "Name": "webdata" } ] }
For more information, see Listing Databases in a Catalog: list-databases
in the Amazon Athena User Guide. -
For API details, see ListDatabases
in Amazon CLI Command Reference.
-
The following code example shows how to use list-named-queries
.
- Amazon CLI
-
To list the named queries for a workgroup
The following
list-named-queries
example lists the named queries for theAthenaAdmin
workgroup.aws athena list-named-queries \ --work-group
AthenaAdmin
Output:
{ "NamedQueryIds": [ "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111", "a1b2c3d4-5678-90ab-cdef-EXAMPLE22222", "a1b2c3d4-5678-90ab-cdef-EXAMPLE33333" ] }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see ListNamedQueries
in Amazon CLI Command Reference.
-
The following code example shows how to use list-query-executions
.
- Amazon CLI
-
To list the query IDs of the queries in a specified workgroup
The following
list-query-executions
example lists a maximum of ten of the query IDs in theAthenaAdmin
workgroup.aws athena list-query-executions \ --work-group
AthenaAdmin
\ --max-items10
Output:
{ "QueryExecutionIds": [ "a1b2c3d4-5678-90ab-cdef-EXAMPLE11110", "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111", "a1b2c3d4-5678-90ab-cdef-EXAMPLE22222", "a1b2c3d4-5678-90ab-cdef-EXAMPLE33333", "a1b2c3d4-5678-90ab-cdef-EXAMPLE11114", "a1b2c3d4-5678-90ab-cdef-EXAMPLE11115", "a1b2c3d4-5678-90ab-cdef-EXAMPLE11116", "a1b2c3d4-5678-90ab-cdef-EXAMPLE11117", "a1b2c3d4-5678-90ab-cdef-EXAMPLE11118", "a1b2c3d4-5678-90ab-cdef-EXAMPLE11119" ], "NextToken": "eyJOZXh0VG9rZW4iOiBudWxsLCAiYm90b190cnVuY2F0ZV9hbW91bnQiOiAxMH0=" }
For more information, see Working with Query Results, Output Files, and Query History
in the Amazon Athena User Guide. -
For API details, see ListQueryExecutions
in Amazon CLI Command Reference.
-
The following code example shows how to use list-table-metadata
.
- Amazon CLI
-
To list the metadata for tables in the specified database of a data catalog
The following
list-table-metadata
example returns metadata information for a maximum of two tables in thegeography
database of theAwsDataCatalog
data catalog.aws athena list-table-metadata \ --catalog-name
AwsDataCatalog
\ --database-namegeography
\ --max-items2
Output:
{ "TableMetadataList": [ { "Name": "country_codes", "CreateTime": 1586553454.0, "TableType": "EXTERNAL_TABLE", "Columns": [ { "Name": "country", "Type": "string", "Comment": "geo id" }, { "Name": "alpha-2 code", "Type": "string", "Comment": "geo id2" }, { "Name": "alpha-3 code", "Type": "string", "Comment": "state name" }, { "Name": "numeric code", "Type": "bigint", "Comment": "" }, { "Name": "latitude", "Type": "bigint", "Comment": "location (latitude)" }, { "Name": "longitude", "Type": "bigint", "Comment": "location (longitude)" } ], "Parameters": { "areColumnsQuoted": "false", "classification": "csv", "columnsOrdered": "true", "delimiter": ",", "has_encrypted_data": "false", "inputformat": "org.apache.hadoop.mapred.TextInputFormat", "location": "s3://awsdoc-example-bucket/csv/countrycode", "outputformat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat", "serde.param.field.delim": ",", "serde.serialization.lib": "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe", "skip.header.line.count": "1", "typeOfData": "file" } }, { "Name": "county_populations", "CreateTime": 1586553446.0, "TableType": "EXTERNAL_TABLE", "Columns": [ { "Name": "id", "Type": "string", "Comment": "geo id" }, { "Name": "country", "Name": "id2", "Type": "string", "Comment": "geo id2" }, { "Name": "county", "Type": "string", "Comment": "county name" }, { "Name": "state", "Type": "string", "Comment": "state name" }, { "Name": "population estimate 2018", "Type": "string", "Comment": "" } ], "Parameters": { "areColumnsQuoted": "false", "classification": "csv", "columnsOrdered": "true", "delimiter": ",", "has_encrypted_data": "false", "inputformat": "org.apache.hadoop.mapred.TextInputFormat", "location": "s3://awsdoc-example-bucket/csv/CountyPopulation", "outputformat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat", "serde.param.field.delim": ",", "serde.serialization.lib": "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe", "skip.header.line.count": "1", "typeOfData": "file" } } ], "NextToken": "eyJOZXh0VG9rZW4iOiBudWxsLCAiYm90b190cnVuY2F0ZV9hbW91bnQiOiAyfQ==" }
For more information, see Showing Metadata for All Tables in a Database: list-table-metadata
in the Amazon Athena User Guide. -
For API details, see ListTableMetadata
in Amazon CLI Command Reference.
-
The following code example shows how to use list-tags-for-resource
.
- Amazon CLI
-
Example 1: To list the tags for a workgroup
The following
list-tags-for-resource
example lists the tags for theData_Analyst_Group
workgroup.aws athena list-tags-for-resource \ --resource-arn
arn:aws:athena:us-west-2:111122223333:workgroup/Data_Analyst_Group
Output:
{ "Tags": [ { "Key": "Division", "Value": "West" }, { "Key": "Team", "Value": "Big Data" }, { "Key": "Location", "Value": "Seattle" } ] }
Example 2: To list the tags for a data catalog
The following
list-tags-for-resource
example lists the tags for thedynamo_db_catalog
data catalog.aws athena list-tags-for-resource \ --resource-arn
arn:aws:athena:us-west-2:111122223333:datacatalog/dynamo_db_catalog
Output:
{ "Tags": [ { "Key": "Division", "Value": "Mountain" }, { "Key": "Organization", "Value": "Retail" }, { "Key": "Product_Line", "Value": "Shoes" }, { "Key": "Location", "Value": "Denver" } ] }
For more information, see Listing the tags for a resource: list-tags-for-resource
in the Amazon Athena User Guide. -
For API details, see ListTagsForResource
in Amazon CLI Command Reference.
-
The following code example shows how to use list-work-groups
.
- Amazon CLI
-
To list workgroups
The following
list-work-groups
example lists the workgroups in the current account.aws athena list-work-groups
Output:
{ "WorkGroups": [ { "Name": "Data_Analyst_Group", "State": "ENABLED", "Description": "", "CreationTime": 1578006683.016 }, { "Name": "AthenaAdmin", "State": "ENABLED", "Description": "", "CreationTime": 1573677174.105 }, { "Name": "primary", "State": "ENABLED", "Description": "", "CreationTime": 1567465222.723 } ] }
For more information, see Managing Workgroups
in the Amazon Athena User Guide. -
For API details, see ListWorkGroups
in Amazon CLI Command Reference.
-
The following code example shows how to use start-query-execution
.
- Amazon CLI
-
Example 1: To run a query in a workgroup on the specified table in the specified database and data catalog
The following
start-query-execution
example uses theAthenaAdmin
workgroup to run a query on thecloudfront_logs
table in thecflogsdatabase
in theAwsDataCatalog
data catalog.aws athena start-query-execution \ --query-string
"select date, location, browser, uri, status from cloudfront_logs where method = 'GET' and status = 200 and location like 'SFO%' limit 10"
\ --work-group"AthenaAdmin"
\ --query-execution-contextDatabase=cflogsdatabase,Catalog=AwsDataCatalog
Output:
{ "QueryExecutionId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111" }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. Example 2: To run a query that uses a specified workgroup to create a database in the specified data catalog
The following
start-query-execution
example uses theAthenaAdmin
workgroup to create the databasenewdb
in the default data catalogAwsDataCatalog
.aws athena start-query-execution \ --query-string
"create database if not exists newdb"
\ --work-group"AthenaAdmin"
Output:
{ "QueryExecutionId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11112" }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. Example 3: To run a query that creates a view on a table in the specified database and data catalog
The following
start-query-execution
example uses aSELECT
statement on thecloudfront_logs
table in thecflogsdatabase
to create the viewcf10
.aws athena start-query-execution \ --query-string
"CREATE OR REPLACE VIEW cf10 AS SELECT * FROM cloudfront_logs limit 10"
\ --query-execution-contextDatabase=cflogsdatabase
Output:
{ "QueryExecutionId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11113" }
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see StartQueryExecution
in Amazon CLI Command Reference.
-
The following code example shows how to use stop-query-execution
.
- Amazon CLI
-
To stop a running query
The following
stop-query-execution
example stops the query that has the specified query ID.aws athena stop-query-execution \ --query-execution-id
a1b2c3d4-5678-90ab-cdef-EXAMPLE11111
This command produces no output.
For more information, see Running SQL Queries Using Amazon Athena
in the Amazon Athena User Guide. -
For API details, see StopQueryExecution
in Amazon CLI Command Reference.
-
The following code example shows how to use tag-resource
.
- Amazon CLI
-
To add a tag to a resource
The following
tag-resource
example adds three tags to thedynamo_db_catalog
data catalog.aws athena tag-resource \ --resource-arn
arn:aws:athena:us-west-2:111122223333:datacatalog/dynamo_db_catalog
\ --tagsKey=Organization,Value=Retail
Key=Division,Value=Mountain
Key=Product_Line,Value=Shoes
Key=Location,Value=Denver
This command produces no output. To see the result, use
aws athena list-tags-for-resource --resource-arn arn:aws:athena:us-west-2:111122223333:datacatalog/dynamo_db_catalog
.For more information, see Adding tags to a resource: tag-resource
in the Amazon Athena User Guide. -
For API details, see TagResource
in Amazon CLI Command Reference.
-
The following code example shows how to use untag-resource
.
- Amazon CLI
-
To remove a tag from a resource
The following
untag-resource
example removes theSpecialization
andFocus
keys and their associated values from thedynamo_db_catalog
data catalog resource.aws athena untag-resource \ --resource-arn
arn:aws:athena:us-west-2:111122223333:datacatalog/dynamo_db_catalog
\ --tag-keysSpecialization
Focus
This command produces no output. To see the results, use the
list-tags-for-resource
command.For more information, see Removing tags from a resource: untag-resource
in the Amazon Athena User Guide. -
For API details, see UntagResource
in Amazon CLI Command Reference.
-
The following code example shows how to use update-data-catalog
.
- Amazon CLI
-
To update a data catalog
The following
update-data-catalog
example updates the Lambda function and description of thecw_logs_catalog
data catalog.aws athena update-data-catalog \ --name
cw_logs_catalog
\ --typeLAMBDA
\ --description"New CloudWatch Logs Catalog"
\ --function=arn:aws:lambda:us-west-2:111122223333:function:new_cw_logs_lambdaThis command produces no output. To see the result, use
aws athena get-data-catalog --name cw_logs_catalog
.For more information, see Updating a Catalog: update-data-catalog
in the Amazon Athena User Guide. -
For API details, see UpdateDataCatalog
in Amazon CLI Command Reference.
-
The following code example shows how to use update-work-group
.
- Amazon CLI
-
To update a workgroup
The following
update-work-group
example disables theData_Analyst_Group
workgroup. Users cannot run or create queries in the disabled workgroup, but can still view metrics, data usage limit controls, workgroup settings, query history, and saved queries.aws athena update-work-group \ --work-group
Data_Analyst_Group
\ --stateDISABLED
This command produces no output. To verify the change in state, use
aws athena get-work-group --work-group Data_Analyst_Group
and check theState
property in the output.For more information, see Managing Workgroups
in the Amazon Athena User Guide. -
For API details, see UpdateWorkGroup
in Amazon CLI Command Reference.
-