Student | Freelance Web Developer | Open Source Developer

About meResume

Create Virtual Views with AWS Glue and Query them Using Athena

Amazon Athena added support for Views with the release of a new version on June 5, 2018. The users can now use CREATE VIEW, DESCRIBE VIEW, DROP VIEW, SHOW CREATE VIEW, and SHOW VIEWS in Athena. The query that defines the view runs each time you reference the view in your query. However, it comes with certain limitations.

Glue allows the creation of tables with type VIRTUAL_VIEW in the Data Catalog. When Athena has been migrated to Data Catalog, if a table of type VIRTUAL_VIEW exists, the database will not list it's tables in the Athena console and we cannot query LIST TABLES; as it returns an error. However, Athena can still query the tables in the database that are not virtual views. Athena will throw an error when tying to query the VIRTUAL_VIEW table.

However, if your use case involves performing SELECT operation on the VIEW from Athena, you can perform the operation by updating the glue.createTable() method of the AWS Glue SDK by changing the parameters as:

  var params = {
    ......
    TableInput: {
        ......
        Parameters: {
            "comment": "Presto View",
            "presto_view": "true"
        }
        StorageDescriptor: {
            Columns: [
                {
                    Name: 'col0',
                    Type: 'bigint'
                },
                {
                    Name: 'col1',
                    Type: 'string'
                }
            ],
            ...........

        },
       ..........
        TableType: 'VIRTUAL_VIEW',
        ViewOriginalText: '/* Presto View: eyJvcmlnaW5hbFNxbCI6IlNFTEVDVFxuICBcImNvbDBcIlxuLCBcImNvbDFcIlxuRlJPTVxuICBrbF9kYXRhMDRcbiIsImNhdGFsb2ciOiJhd3NkYXRhY2F0YWxvZyIsInNjaGVtYSI6ImRlZmF1bHQiLCJjb2x1bW5zIjpbeyJuYW1lIjoiY29sMCIsInR5cGUiOiJiaWdpbnQifSx7Im5hbWUiOiJjb2wxIiwidHlwZSI6InZhcmNoYXIifV19 */',
        ViewExpandedText: '/* Presto View */'
    },
};

In the above code snippet, the value for the "ViewOriginalText" contains the base64 encoded format of the text:

  {
    "originalSql":"SELECT  \"col0\", \"col1\"FROM kl_data04",
    "catalog":"awsdatacatalog",
    "schema":"default",
    "columns":[
      {
        "name":"col0",
        "type":"bigint"
      },
      {
        "name":"col1",
        "type":"varchar"
      }
    ]
  }