CODEWITHSUNDEEP
amazon-web-servicesamazon-athena
Kb.
Kb.

Reputation: 7400

Ways to backup AWS Athena views

In an AWS Athena instance we have several user-created views.
Would like to back-up the views.
Have been experimenting using AWS CLI
aws athena start-query-execution --query-string “show views...
and for each view
aws athena start-query-execution --query-string “show create views...
and then
aws athena get-query-execution --query-execution-id...
to get the s3 location for the create view code.

Looking for ways to get the view definitions backed up.
If AWS CLI is the best suggestion, then I will create a Lambda to do the backup.

Upvotes: 1

Views: 1308

Answers (1)

Zerodf
Zerodf

Reputation: 2298

I think SHOW VIEWS is the best option.

Then you can get the Data Definition Language (DDL) with SHOW CREATE VIEW.

There are a couple of ways to back the views up. You could use GIT (AWS offers CodeCommit). You could definitely leverage CodeCommit in a Lambda Function using Boto3.

In fact, just by checking the DDL, you are in fact backing them up to [S3].

Consider the following DDL:

CREATE EXTERNAL TABLE default.dogs (
  `breed_id` int, 
  `breed_name` string, 
  `category` string
)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
  LINES TERMINATED BY '\n' 
LOCATION
  's3://stack-exchange/48836509'
TBLPROPERTIES ('skip.header.line.count'='1')

and the following view based on it.

CREATE VIEW default.vdogs AS SELECT * FROM default.dogs;

When we show the DDL:

$ aws athena start-query-execution --query-string "SHOW CREATE VIEW default.vdogs" --result-config
uration OutputLocation=s3://stack-exchange/66620228/
{
    "QueryExecutionId": "ab21599f-d2f3-49ce-89fb-c1327245129e"
}

We write to S3 (just like any Athena query).

$ cat ab21599f-d2f3-49ce-89fb-c1327245129e.txt
CREATE VIEW default.vdogs AS
SELECT *
FROM
  default.dogs

Upvotes: 1

Related Questions