BigQuery cli bq how to use EXECUTE IMMEDIATE script

I would like to use two SQL scripts to create and update a table in BigQuery using bq query command. Here are the two scripts :

1- Creating the table

EXECUTE IMMEDIATE 
    '''CREATE TABLE IF NOT EXISTS dataset.newtable (
        id STRING, ''' ||
        (SELECT STRING_AGG(
                segment || 
                " INT64 " ORDER BY segment
        ) FROM (
            SELECT DISTINCT segment FROM dataset.source
        )
    ) || 
    ''')''';

2- updating the table

EXECUTE IMMEDIATE 
    '''INSERT dataset.newtable (
        SELECT id, ''' ||
        ( SELECT STRING_AGG("COUNTIF(
            segment = '" ||
            segment ||
        "') AS " ||
        segment ORDER BY segment
    ) FROM (
        SELECT DISTINCT segment FROM dataset.source
    ) ) || 
    ''' FROM dataset.source GROUP BY 1 ORDER BY 1
)''';

When using these two scripts as queries with the bq query command, I have got an error :

Syntax error: Expected keyword IMMEDIATE but got to end of the script at [1:8]

Is there any other way to do that?

Thank you

Answer

The default query dialect in the bq command-line tool is legacy SQL. You need to switch to the standard SQL dialect to be able to run scripts:

1. --use_legacy_sql=false or --nouse_legacy_sql flag to your command-line statement.
2. prefix your sql with #standardSQL

You can also set standard SQL as your default. See here.