Method 1: Adding Columns Individually

To set up a table in Athena from S3, follow these steps:

1. Open the Athena console in AWS Management Console.
2. Select the "Create" button, then "S3 bucket data" for table creation.
3. In the "Table name" dialog, input your desired table name.
4. Choose to create a new database or select an existing one.
5. Specify the S3 bucket containing your data files.
6. Select the file format (e.g., CSV) and choose the appropriate SerDe Library.

For CSV files, you may use:

• LazySimpleSerDe for unquoted fields.
• OpenCSVSerDe for quoted fields.

7. Define the columns based on your S3 data files, adding new columns as needed.
8. Optionally, include additional table properties, such as skipping the header row or adding partition columns, which help organize your data into manageable sections.
9. Finally, click the "Create Table" button.

Method 2: Bulk Column Addition

Athena also provides a bulk add column feature, which is beneficial for:

• Datasets with numerous columns.
• Fixed schemas that rarely change.
• Rapidly creating new tables from scratch.

To bulk add columns:

1. Follow the previous steps until the column definition section.
2. Click "Bulk add columns."
3. Enter the column names and data types as comma-separated pairs, then click "Add."
4. Verify that all columns are correctly added before proceeding.

Method 3: Creating a Table Using SQL Query

You can directly create a table from S3 files by executing an SQL query in the Athena query editor. Here’s a sample query to create a table:

CREATE EXTERNAL TABLE IF NOT EXISTS default.`test_table` (

id int,

name string,

age int

)

PARTITIONED BY (age int)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

WITH SERDEPROPERTIES ('field.delim' = ',')

LOCATION 's3://test-bucket/test-folder/'

TBLPROPERTIES (

'classification' = 'csv',

'skip.header.line.count' = '1'

);

Method 4: Using Python Code (Boto3)

You can also create an Athena table using the AWS SDK for Python (Boto3). Here’s a brief example:

import boto3

# Create Athena client

client = boto3.client('athena')

# Define S3 input location and table name

s3_input_location = 's3://your-bucket-name/your-folder/'

table_name = 'your-table-name'

# Define create table SQL statement

create_table_sql = '''

CREATE EXTERNAL TABLE IF NOT EXISTS {table_name} (

column1 string,

column2 int,

column3 double

)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

WITH SERDEPROPERTIES (

'serialization.format' = ',',

'field.delim' = ','

) LOCATION '{s3_input_location}'

TBLPROPERTIES ('classification' = 'csv')

'''.format(table_name=table_name, s3_input_location=s3_input_location)

# Submit query to create table

response = client.start_query_execution(

QueryString=create_table_sql,

QueryExecutionContext={

'Database': 'your-database-name'

},

ResultConfiguration={

'OutputLocation': 's3://your-bucket-name/your-output-folder/'

}

)

# Wait for query to complete

query_execution_id = response['QueryExecutionId']

result = client.get_query_execution(QueryExecutionId=query_execution_id)

state = result['QueryExecution']['Status']['State']

while state == 'RUNNING':

result = client.get_query_execution(QueryExecutionId=query_execution_id)

state = result['QueryExecution']['Status']['State']

# Check if table was created successfully

if state == 'SUCCEEDED':

print(f'Table {table_name} created successfully!')

else:

print('Table creation failed.')

Method 5: Using AWS Wrangler

The AWS Wrangler library simplifies creating Athena tables from existing S3 files in Python. Here’s an example:

import awswrangler as wr

wr.catalog.create_csv_table(

database='default',

table='my_table',

path='s3://bucket/prefix/',

columns_types={'col0': 'bigint', 'col1': 'double'},

partitions_types={'col2': 'date'},

description='My own table!',

columns_comments={'col0': 'Column 0.', 'col1': 'Column 1.', 'col2': 'Partition.'}

)

Method 6: Creating Tables Directly from DataFrames

You can create an Athena table from a Pandas DataFrame using AWS Wrangler. Here’s a sample code:

import awswrangler as wr

import pandas as pd

# Generate a Pandas DataFrame

df = pd.DataFrame({

'col1': [1, 2, 3, 4],

'col2': ['a', 'b', 'c', 'd'],

'col3': [0.1, 0.2, 0.3, 0.4]

})

# Define the table schema

columns_types = {

'col1': 'int',

'col2': 'varchar(10)',

'col3': 'double'

}

# Define S3 bucket and S3 path

bucket_name = 'my-bucket'

s3_path = f's3://{bucket_name}/my-folder'

# Save the dataframe to table and S3

wr.s3.to_parquet(

df=df,

path=s3_path,

dataset=True,

mode='append',

database='my_database',

table='my_table',

partition_cols=['col2'],

)

# Execute a SQL query

result_df = wr.athena.read_sql_query(

sql='SELECT * FROM my_database.my_table',

database='my_database'

)