Creating a table in BigQuery using the PHP api with schema

Question

I'm trying to create a table in bigquery using the php api.

I can create a table without a schema just fine, but when I provide a schema I get errors. It looks like I'm using the wrong syntax, however I tried just about any formatting I could think of and couldn't find a single example of what I'm trying to achieve.

I use a string literal for the fields parameter for testing. My code looks like this:

    $bigQuery = new BigQueryClient([
        'keyFilePath' => [keyfilepath],
        'projectId' => [projectid],
        'location' => [location]
    ]);

    /** @var Dataset $dataSet */
    $dataSet = $bigQuery->dataset('my-data-set');

    $fieldString = '{"name": "myfield","type": "STRING","mode": "REQUIRED"}' . "\n" . '{"name": "anotherfield", "type": "STRING", "mode": "REQUIRED"}';
    $options = [
        'fields' => $fieldString
    ];
    $dataSet->createTable('mytable', $options);

Which gives the error:

"Invalid field selection {\"name\":\"myfield\""

Or alternatively, when I format the "$fieldString" like this:

$fieldString = '[{"name": "myfield","type": "STRING","mode": "REQUIRED"}, {"name": "anotherfield", "type": "STRING", "mode": "REQUIRED"}]';

I get the error:

Invalid FieldMask '[{\"name\":\"myfield\",\"type\":\"STRING\",\"mode\":\"REQUIRED\"},{\"name\":\"anotherfield\",\"type\":\"STRING\",\"mode\":\"REQUIRED\"}]'. Map keys should be represented as [\"some_key\"].

I've also tried to create the table first and then update it like so:

$table = $dataSet->createTable('mytable');
$table->update($options);

But I get the same errors. Even when I use the the json representation exactly like shown here the problem persists.

What am I doing wrong here?

UPDATE:

I actually first tried this, before I switched to a string literal for the fields:

    $fields = [
        ['name'=> 'myfield', 'type' => 'INTEGER', 'mode' => 'REQUIRED'],
        ['name'=> 'anotherfield', 'type' => 'INTEGER', 'mode' => 'REQUIRED']
    ];
    $options = [
        'schema' => $fields
    ];
    $dataSet->createTable('mytable', $options);

This yields the error:

"Invalid JSON payload received. Unknown name \"schema\" at 'table': Proto field is not repeating, cannot start list."

Then I edited the code to look like this:

    $fields = [
        ['name'=> 'myfield', 'type' => 'INTEGER', 'mode' => 'REQUIRED'],
        ['name'=> 'anotherfield', 'type' => 'INTEGER', 'mode' => 'REQUIRED']
    ];
    $options = [
        'fields' => $fields
    ];
    $dataSet->createTable('mytable', $options);

Which gives:

Warning: rawurlencode() expects parameter 1 to be string, array given

I didn't mention this in my question before because I thought it wasn't relevant. In hindsight it probably is, but my problem still persists.

Pentium10 Pentium10 · Accepted Answer · 2020-02-11T13:28:42

There is no such thing as $fieldString that is $fields array

like this:

 $fields = [
    [
        'name' => 'field1',
        'type' => 'string',
        'mode' => 'required'
    ],
    [
        'name' => 'field2',
        'type' => 'integer'
    ],
];

then

$schema = ['fields' => $fields];
$table = $dataset->createTable($tableId, ['schema' => $schema]);

See example here.

Creating a table in BigQuery using the PHP api with schema

1 Answers