CODEWITHSUNDEEP
amazon-web-servicesterraformaws-glueamazon-athena
SimonB
SimonB

Reputation: 1055

Glue Virtual View (terraform created) not appearing in Athena

I am trying to use Terraform aws_glue_catalog_table to create a Virtual_View , which I understand, should appear in Athena as a View.

So far my code seems to create a catalog table in Glue, but nothing appears in the Athena Views inventory.

It is hard to know exactly which part is the issue. I have tried comparing the generated glue table to an existing one, manually created but of the same specification, but no differences appear in the info shown - but the 'originaltext' part is hard to compare being encoded.

Have tried removing the ser_de_info section, but doesn't seem to make any difference.

Grateful for any hints here !

Really not sure why TF doesn't just allow us to submit a simple SQL DDL statement to create these, as this glue method is just too convoluted to make practical sense - declaring columns twice in 2 different formats , encoding script - both just bad

resource "aws_glue_catalog_table" "aws_gluetable_getresources_vw" {
    name = "getresources_vw" 
    database_name = "mydatabase" 
    table_type = "VIRTUAL _VIEW"

    view_original_text = "/* Presto View: ${base64encode(file("${path.module}/originaltexts/getresources.txt"))} */"
    view_expanded_text = "/* Presto View */" 

    parameters = {
        presto_view = "true"
        comment = "Presto View"
    }

    storage_descriptor { 
        ser_de_info {
            name = " " 
            serialization_library = " " 
        } 

        columns {
            name = "key" 
            type = "string"
        }
        columns {
            name = "value" 
            type = "string"
        }
        columns {
            name = "resourcearn" 
            type = "string"
        }
        columns {
            name = "tags" 
            type = "array<struct<key:string,value:string>>"
        }
         .... more
        }
    }
}

getresources.txt

{
    "catalog":"awsdatacatalog",
    "schema":"mydatabase",
    "columns":[
        {"name":"key","type":"varchar"},
        {"name":"value","type":"varchar"},
        {"name":"resourcearn","type":"varchar"},
        {"name":"tags","type":"array(row(key varchar,value varchar))"},
        {"name":"arn1","type":"varchar"},
        {"name":"arn2","type":"varchar"},
        {"name":"arn3","type":"varchar"},
        {"name":"arn4","type":"varchar"}
    ],
    "originalSql":"SELECT  g.tag.key, g.tag.value, t.resource.resourcearn, t.resource.tags, split_part(t.resource.resourcearn, ':', 1) arn1, split_part(t.resource.resourcearn, ':', 2) arn2, split_part(t.resource.resourcearn, ':', 3) arn3, split_part(t.resource.resourcearn, ':', 6) arn4 FROM  ((ap_ath_meta_use_sbx.getresources h CROSS JOIN UNNEST(h.resourcetagmappinglist) t (resource)) CROSS JOIN UNNEST(t.resource.tags) g (tag))"
}

Upvotes: 2

Views: 2827

Answers (1)

Theo
Theo

Reputation: 132922

Creating an Athena-compatible view using the Glue APIs is difficult. I don't know how the Terraform provider does it, but I assume it's missing one of the many details that are necessary to get right. I wrote together an unofficial documentation in this answer: https://stackoverflow.com/a/56347331/1109

Upvotes: 2

Related Questions