Google Cloud Spanner - Unable to create table with REST API

1
votes

I'm trying to remotely create a table in a Cloud Spanner database using the REST API. The SQL I'm attempting to run is:

CREATE TABLE test (id INT64 NOT NULL) PRIMARY KEY (id)

My initial attempt used the executeSql endpoint, and that resulted in a less than helpful error message:

Syntax error: Expected \")\" or \",\" but got keyword NOT [at 1:29]\nCREATE TABLE test (id INT64 NOT NULL) PRIMARY KEY (id)\n

After a while I discovered the updateDdl endpoint but when I try this by supplying the above query in the statements list, I just get an empty 200 response back and no table is created.

How can I create a table on Cloud Spanner using SQL?

Update

It turns out I was doing everything correct, except a bug in my code meant I wasn't sending a PATCH request, I was sending a GET request! Rather than returning a 405, Spanner happily returns a 200 in this case, but does nothing :(

2
restgoogle-cloud-platformgoogle-cloud-spanner
The SQL statement should be: CREATE TABLE test (id INT64 NOT NULL, PRIMARY KEY (id)); – putu
Doesn't help, still same behaviour :( – Kazade

2 Answers

2
votes

updateDdl is the correct method call. Note, with a 200 response, it will return a long running operation since the schema change is not instantaneous:

The returned long-running operation will have a name of the format /operations/ and can be used to track execution of the schema change(s). The metadata field type is UpdateDatabaseDdlMetadata. The operation has no response.

It looks like the REST API excludes the semicolon at the end:

PATCH https://spanner.googleapis.com/v1/projects/YOUR_PROJECT/instances/YOUR_INSTANCE/databases/YOUR_DATABASE/ddl

{
  "statements": [
    "CREATE TABLE Tests (TestId INT64 NOT NULL,) PRIMARY KEY(TestId)"
  ]
}

You can also create schemas via a UI in the Cloud Console.

1
votes

Try running

CREATE TABLE Tests (
    TestId INT64 NOT NULL,
) PRIMARY KEY(TestId);