SQLBuilder Class

ibmcloudsql.sql_magic provides the following three classes:

• TimeSeriesTransformInput class: provides utilities for mapping user-friendly APIs to library-friendly APIs

• TimeSeriesSchema class: provides hints about the schema of the data to support get_ts_datasource()

• SQLBuilder class: provides APIs to help constructing a complete SQL query without knowing the details about syntax specific to IBM Cloud SQL

Time series transform input

A TimeSeriesTransformInput class: provides utilities for mapping from user-friendly time series query into library-friendly time series query

Example:

sql_stmt = """
ts_segment_by_time(ts, week, week)
"""
sql_stmt = """
ts_segment_by_time(ts, 604800000, 604800000)
"""

• transform_sql(): the decorator that is applied on the SQLBuilder.print_sql() and SQLBuilder.format_() methods

• ts_segment_by_time()

Time series schema

• unixtime_columns: shows which columns contain time stamp data in Unix time format

Time series schema SQL Magic

A SQLBuilder class is also a TimeSeriesSchema class.

• reset_(): resets the internal storage of an SQL statement (use this before constructing a new one)

• print_sql(): prints and checks the current content of an SQL statement

• get_sql(): returns the string representation of the SQL statement

• with_(): provides table name and the SQL query for that table

• select_(): provides column names

• from_table_(): the table name

• from_cos_(): provides COS URL and format of data via format_type option

• from_view_(): provides SQL statement that returns a view

• where_(): where condition

• order_by_(): lists columns

• group_by_(): lists columns

• store_at_(): provides COS URL and format of data

• partition_objects_(): provides number of objects

• partition_rows_(): provides number of rows per object

• partition_by_(): provides the string of tuple of column names for HIVE catalog partitioning

• format_(): applies transformation needed to map user-friendly time series queries into library-friendly time series queries

• join_cos_(): JOIN statement using COS URL

• join_table_(): JOIN statement using table name

Example: we can generate the SQL string using SQLBuilder APIs

sqlmagic = ibmcloudsql.SQLBuilder()
(sqlClient
 .with_("humidity_location_table",
          (sqlmagic.select_("location")
                   .from_view_("select count(*) as count, location from dht where humidity > 70.0 group by location")
                   .where_("count > 1000 and count < 2000")
          ).reset_()
       )
 .with_("pm_location_table",
          (sqlmagic.select_("location")
                   .from_view_("select count(*) as count, location from sds group by location")
                   .where_("count > 1000 and count < 2000")
          ).reset_()
       )
 .select_("humidity_location_table.location")
 .from_table_("humidity_location_table")
 .join_table_("pm_location_table", type="inner", condition="humidity_location_table.location=pm_location_table.location")
 .store_at_(targeturl)
 )
result = sqlClient.run()

This is the typically way to generate the SQL similar to the one above

stmt = """
    WITH
        humidity_location_table AS (
        -- 1. Select locations from DHT where humidity is >70% and the length is data is between 1000 and 2000
            SELECT location from (
                SELECT
                    COUNT(*) AS count,
                    location
                FROM DHT
                WHERE humidity > 70.0
                GROUP BY location
            )
            WHERE count > 1000 AND count < 2000
        ),
        pm_location_table AS (
        -- 2. Select locations from PM where length is data is between 1000 and 2000
           SELECT location from (
            SELECT
              COUNT(*) AS count,
              location
            FROM SDS
            GROUP BY location
          )
          WHERE count > 1000 AND count < 2000
        )
        -- 3. Select those locations that are present in both PM and DHT tables
        SELECT
            humidity_location_table.location
        FROM humidity_location_table
        INNER JOIN pm_location_table
        ON humidity_location_table.location=pm_location_table.location
        INTO {}
""".format(targeturl)
result = sqlClient.execute_sql(stmt)