Skip to Content

How to create a Cloud Spanner database from long DDL

With the help of a little bash script

Posted on 2 mins read

Cloud Spanner supports DDL operations (Data Definition Language) to:

  • Create a database.
  • Create, alter, or drop tables in a database.
  • Add, alter, or drop columns in a table.
  • Create or drop indexes in a database.

To load a really long DDL, you can do so via the console: when clicking on the Create Table action when you edit a database, or when you click on the Edit Schema action when you edit a table, you can flip the switch Edit as Text, which will allow you to enter the DDL in a big text area:

Switch to text mode for editing DDLs

But to load a really long DDL, you might want to use gcloud on the command-line instead, and the following bash script might come in handy to parse and execute each DDL command:

#!/bin/bash

# Define path to your DDL
# export SPANNER_DB_DDL="./my-project-v1-2.ddl"

echo 'Loading DDL...'
echo 'NOTE: empty @type property warning on return protobuf message are OK'
DDL=`cat ${SPANNER_DB_DDL} | tr -d '\n' | tr -d '\r' | tr -d '\t'`
IFS=';' read -ra LINES <<< "$DDL"
for SQL in "${LINES[@]}"; do
    # echo $SQL
    if [ ${#SQL} -ge 5 ]; then
      gcloud spanner databases ddl update $SPANNER_DB --instance=$SPANNER_INSTANCE --ddl="$SQL"
    fi
done

More information

Read this article putting this tip into action: Using Google Cloud Spanner to measure social media influence over stock market.

comments powered by Disqus