How do I create a BigQuery view with standard SQL using the Java API?

Question

I can create views using the Java API, but the query needs to be legacy sql:

public void createView(String dataSet, String viewName, String query) throws Exception {

    Table content = new Table();
    TableReference tableReference = new TableReference();
    tableReference.setTableId(viewName);
    tableReference.setDatasetId(dataSet);
    tableReference.setProjectId(projectId);
    content.setTableReference(tableReference);

    ViewDefinition view = new ViewDefinition();

    view.setQuery(query);
    content.setView(view);
    LOG.debug("View to create: " + content);
    try {
        if (tableExists(dataSet, viewName)) {
            bigquery.tables().delete(projectId, dataSet, viewName).execute();
        }
    } catch (Exception e) {
        LOG.error("Could not delete table", e);
    }

    bigquery.tables().insert(projectId, dataSet, content).setProjectId(projectId).execute();
}

Is there a way to create a BQ view with standard sql using the API?

I'm not seeing a related option in the API docs. What if you use #standardSQL at the start of the query itself? — Elliott Brossard
– Elliott Brossard, Commented Jun 9, 2017 at 20:32

Graham Polley · Accepted Answer · 2017-06-10 12:39:31Z

2

You need to set setUseLegacySQL(false) on the ViewDefinition object.

[..]
ViewDefinition view = new ViewDefinition();
view.setQuery(query);
view.setUseLegacySql(false); //<-- you're missing this
content.setView(view);
[..]

See the API docs here.

answered Jun 10, 2017 at 12:39

Graham Polley

14.8k9 gold badges54 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CCC Over a year ago

you are right. I was using an older version of the api

Andrey Fedorov Over a year ago

here's a link to the "new" version (ignore the big red "this is the old version" warning): developers.google.com/api-client-library/java/apis/bigquery/v2 /boggle

Graham Polley Over a year ago

@AndreyFedorov - not following you. That's the older API. The new (more idiomatic) one is here: github.com/GoogleCloudPlatform/google-cloud-java/tree/master/…

Andrey Fedorov Over a year ago

oooh so new so idiomatic. could you point me to the setUseLegacySql method you are talking about? it seems my command-f is broken github.com/GoogleCloudPlatform/google-cloud-java/blob/master/…

Sourygna Over a year ago

indeed, the new API unfortunately no longer offers this method : stackoverflow.com/questions/46813165/…

Sourygna · Accepted Answer · 2017-12-15 08:58:34Z

0

With the new API, you can use the "#standardSQL" notation to avoid the default LegacySQL setting (the setUseLegacySql() method no longer exists in the new API).

Here is an example:

ViewDefinition tableDefinition = ViewDefinition.newBuilder("#standardSQL\n  WITH A AS (select 1 as foo) SELECT * from A").build();

answered Dec 15, 2017 at 8:58

Sourygna

7194 silver badges16 bronze badges

Collectives™ on Stack Overflow

How do I create a BigQuery view with standard SQL using the Java API?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related