AWS Athena create table from select query

Question

I am trying to create table using the query below. If I do not create table, but just run the part from SELECT *, the query can be run.

    (SELECT *
    FROM "MyDatabase"."2007" A
    WHERE A."column name a" NOT IN ('U','A+','A','A-')
            AND A."column name b" NOT IN ('SHH','CTP')
            AND NOT EXISTS 
        (SELECT *
        FROM "MyDatabase"."2008" B
        WHERE (B."column name a" = A."column name a"
                AND B."column name b" = A."column name b"
                AND B."column name c" = A."column name c")))

The error message is "GENERIC_INTERNAL_ERROR: field ended by ';': expected ';' but got 'partOfAColName' at line 1:..."

From google search, space in column names seems to be the problem. But I am not sure. I have space in column names. The column names are automatically detected by Glue Crawler. So I am not sure if I can do anything about it. I have around 20 columns, all having space in the middle though. Could someone suggest a fix? Thanks.

Denise M Denise M · Accepted Answer · 2020-02-05T22:24:54

When you execute CREATE TABLE AS...you are telling Athena to create a table with the same column names in your SELECT, but in this case those column names contain spaces, and Athena won't allow you to create a column name with a space. To avoid this you can create the table with column names that adhere to the Athena's specifications then populate that table with INSERT INTO SELECT...FROM

AWS Athena create table from select query

1 Answers