aws glue / pyspark - how to create Athena table programmatically using Glue

Question

I am running a script in AwsGlue which loads the data from s3, does some transformation and saves the results to S3. I am trying to add one more step to this routine. I want to create a new table in an existing database in Athena.

I cannot find any similar example in AWS documentation. The results are just written down to S3 in the examples I came across. Is this possible in Glue?

There is some example of the code. How should it be modified to create the Athena table with the output results?

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.dynamicframe import DynamicFrame

from pyspark.sql import SparkSession
from pyspark.context import SparkContext
from pyspark.sql.functions import *
from pyspark.sql import SQLContext
from pyspark.sql.types import *


args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)


datasource0 = glueContext.create_dynamic_frame.from_catalog(database = "dataset", table_name = "table_1", transformation_ctx = "datasource0")
applymapping1 = ApplyMapping.apply(frame = datasource0, mappings = [("id", "long", "id", "long"), ("description", "string", "description", "string")], transformation_ctx = "applymapping1")
resolvechoice2 = ResolveChoice.apply(frame = applymapping1, choice = "make_struct", transformation_ctx = "resolvechoice2")
dropnullfields3 = DropNullFields.apply(frame = resolvechoice2, transformation_ctx = "dropnullfields3")
datasink4 = glueContext.write_dynamic_frame.from_options(frame = dropnullfields3, connection_type = "s3", connection_options = {"path": "s3://..."}, format = "parquet", transformation_ctx = "datasink4")


*create Athena table with the output results*

job.commit()

Javier Ramirez Javier Ramirez · Accepted Answer · 2019-05-31T12:32:13

I can think of two ways to do this. One is using the sdk to get a reference to the athena API and use it to execute a query with the create table statement, as seen at this blog post

An alternative way which might be more interesting is using the Glue API to create a crawler for your S3 bucket and then execute the crawler.

With the second approach your table is catalogued and you can use it not only from athena, but also from EMR, or Redshift spectrum.

aws glue / pyspark - how to create Athena table programmatically using Glue

1 Answers