Athena examples using Amazon CLI - Amazon Command Line Interface
Actions
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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 and cross-service examples.

Scenarios are code examples that show you how to accomplish a specific task by calling multiple functions within the same service.

Each example includes a link to GitHub, 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.

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.

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 the dynamo_db_catalog data catalog.

aws athena create-data-catalog \
    --name dynamo_db_catalog \
    --type LAMBDA \
    --description "DynamoDB Catalog" \
    --parameters function=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.

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 the AthenaAdmin workgroup that queries the flights_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." \
    --database sampledb \
    --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-group AthenaAdmin

Output:

{
    "NamedQueryId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111"
}

For more information, see Running SQL Queries Using Amazon Athena in the Amazon Athena User Guide.

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 called Data_Analyst_Group that has the query results output location s3://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" \
    --tags Key=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.

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 the UnusedDataCatalog 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.

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.

The following code example shows how to use delete-work-group.

Amazon CLI

To delete a workgroup

The following delete-work-group example deletes the TeamB 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.

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 the dynamo_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 the sampledb database in the AwsDataCatalog data catalog.

aws athena get-database \
    --catalog-name AwsDataCatalog \
    --database-name sampledb

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.

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.

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 the counties table, including including column names and their datatypes, from the sampledb database of the AwsDataCatalog data catalog.

aws athena get-table-metadata \
    --catalog-name AwsDataCatalog \
    --database-name sampledb \
    --table-name counties

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.

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 the AthenaAdmin 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.

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 the AwsDataCatalog 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 the AthenaAdmin 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.

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 the AthenaAdmin workgroup.

aws athena list-query-executions \
    --work-group AthenaAdmin \
    --max-items 10

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.

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 the geography database of the AwsDataCatalog data catalog.

aws athena list-table-metadata \
    --catalog-name AwsDataCatalog \
    --database-name geography \
    --max-items 2

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.

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 the Data_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 the dynamo_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.

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 the AthenaAdmin workgroup to run a query on the cloudfront_logs table in the cflogsdatabase in the AwsDataCatalog 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-context Database=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 the AthenaAdmin workgroup to create the database newdb in the default data catalog AwsDataCatalog.

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 a SELECT statement on the cloudfront_logs table in the cflogsdatabase to create the view cf10.

aws athena start-query-execution \
    --query-string  "CREATE OR REPLACE VIEW cf10 AS SELECT * FROM cloudfront_logs limit 10" \
    --query-execution-context Database=cflogsdatabase

Output:

{
"QueryExecutionId": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11113"
}

For more information, see Running SQL Queries Using Amazon Athena in the Amazon Athena User Guide.

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.

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 the dynamo_db_catalog data catalog.

aws athena tag-resource \
    --resource-arn arn:aws:athena:us-west-2:111122223333:datacatalog/dynamo_db_catalog \
    --tags Key=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 the Specialization and Focus keys and their associated values from the dynamo_db_catalog data catalog resource.

aws athena untag-resource \
    --resource-arn arn:aws:athena:us-west-2:111122223333:datacatalog/dynamo_db_catalog \
    --tag-keys Specialization 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 the cw_logs_catalog data catalog.

aws athena update-data-catalog \
    --name cw_logs_catalog \
    --type LAMBDA \
    --description "New CloudWatch Logs Catalog" \
    --function=arn:aws:lambda:us-west-2:111122223333:function:new_cw_logs_lambda

This 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.

The following code example shows how to use update-work-group.

Amazon CLI

To update a workgroup

The following update-work-group example disables the Data_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 \
    --state DISABLED

This command produces no output. To verify the change in state, use aws athena get-work-group --work-group Data_Analyst_Group and check the State property in the output.

For more information, see Managing Workgroups in the Amazon Athena User Guide.