BigQuery Node.js api to create external table

Question

I am trying to create an external table from a google cloud function using the node.js API. The function will trigger from GCS bucket changes. I can create a native table but not an external table. In the node.js api for import here, the configuration.load metadata does not have a setting to specify this as external table. Here is my code for native table creation till now. My question is "How do I use the Node.js Api to create external table for a GCS bucket"

    const projectId = "N"
    const bigquery = BigQuery({
        projectId: projectId
    });
    const storage = Storage({
        projectId: projectId
    });

    const dataset = bigquery.dataset("dataset");

    const table = dataset.table("test_data");

    const bucket = storage.bucket("my-bucket");

    const file = bucket.file("2017/03/02/*");

    let job;

    // Imports data from a GCS file into a table
    return table.import(file,{"sourceFormat":"AVRO"})
        .then((results) => {
            job = results[0];
            console.log(`Job ${job.id} started.`);
            return job.promise();
        })
        .then((results) => {
            console.log(`Job ${job.id} completed.`);
            return results;
        });

Ram Ram · Accepted Answer · 2017-03-21T00:13:42

I found out the solution after reading the docs a bit. Federated tables don't work by loading/importing them. They are just tabled with externalConfig set. So this code worked for me

const dataset = bigquery.dataset("dataset");
var options = {
    "externalDataConfiguration": {
        "sourceUris": ["gs://my-bucket/2017/03/20/*"],
        "sourceFormat": "AVRO",
        "maxBadRecords": 0,
        "autodetect": true
    }
}
dataset.createTable("test_data_20170320",options, function(err, table, apiResponse){
    console.log(err)
    console.log(table)
    console.log(apiResponse)
})

BigQuery Node.js api to create external table

1 Answers