SQLMagic 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()

  • SQLMagic 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)
"""

Time series schema

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

Time series schema SQL Magic

A SQLMagic class is also a TimeSeriesSchema class.

Example: we can generate the SQL string using SQLMagic APIs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
sqlmagic = ibmcloudsql.SQLMagic()
(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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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)