pythonsqlsqlalchemysinglestorecolumnstore

Create tables similar to existing using Python's Sqlalchemy and Singlestore sql

I have a set of existing tables in a singlestore database, many of which are columnstore tables.

I want to build a python script that will migrate my development tables to production tables. The dev tables are already built, analyzed, and compared with existing tables, but instead of hardcoding sql scripts to replace existing prod tables with dev, I want a robust python way of performing this operation.

Below is the code I have thus far:

import sqlalchemy
import datalake_toolset as tls # customized module for personal use

# connect to database and obtain metadata
engine = tls.create_lake_engine() # just creates my engine for my database
conn = engine.connect()
metadata = sqlalchemy.MetaData(conn)
metadata.reflect()

# identify the tables and isolate to interested tables only
tables = metadata.tables
tablenames = [x.name for x in tables.values() 
              if x.name.startswith('NamesOfInterest')]

# start with an example table and see if we can create a prod
table = [x for x in tablenames if 'SomeExampleString' in x][0]

# obtain the metadata for the new prod table
table_meta = tables.get(table)

# change table metadata name
table_meta.name = table_meta.name.replace('_Dev_', '_Prod_')

# now go create the prod table

This is where I get stuck... how can I create the new, Prod table with the same structure and datatypes as the Dev table, while retaining the columnstore feature (some of these datasets will be a millions of records, and I have business constraints against the default rowstore for tables that large).

Solution

  • I don't know much about sqlalchemy, but you can use CREATE TABLE LIKE to create a new table with the same schema as an existing table. Something like

    CREATE TABLE t_prod LIKE t_dev;

    If t_dev is a columnstore table, t_prod will be as well (their show create table output will be identical)