<jupyter_start><jupyter_text>Querying IBM Db2 Event StoreIBM Db2 Event Store is a hybrid transactional/analytical processing (HTAP) system. This notebook will demonstrate the best practices for querying a table stored in IBM Db2 Event Store.***Pre-Req: Event_Store_Table_Creation***  Connect to IBM Db2 Event Store Determine the IP address of your hostObtain the IP address of the host that you want to connect to by running the appropriate command for your operating system:* On Mac, run: `ifconfig`* On Windows, run: `ipconfig`* On Linux, run: `hostname -i`Edit the `HOST = "XXX.XXX.XXX.XXX"` value in the next cell to provide the IP address.<jupyter_code># Set your host IP address
HOST = "XXX.XXX.XXX.XXX"

# Port will be 1100 for version 1.1.2 or later (5555 for version 1.1.1)
PORT = "1100"

# Database name
DB_NAME = "TESTDB"

# Table name
TABLE_NAME = "IOT_TEMPERATURE"<jupyter_output><empty_output><jupyter_text>Import Python modules<jupyter_code>from eventstore.common import ConfigurationReader
from eventstore.oltp import EventContext
from eventstore.sql import EventSession
from pyspark.sql import SparkSession<jupyter_output><empty_output><jupyter_text>Connect to Event Store<jupyter_code>endpoint = HOST + ":" + PORT
print("Event Store connection endpoint:", endpoint)
ConfigurationReader.setConnectionEndpoints(endpoint)<jupyter_output><empty_output><jupyter_text>Open the databaseThe cells in this section are used to open the database and create a temporary view for the table that we created previously.    To run Spark SQL queries, you first have to set up a Db2 Event Store Spark session. The EventSession class extends the optimizer of the SparkSession class.<jupyter_code>sparkSession = SparkSession.builder.appName("EventStore SQL in Python").getOrCreate()
eventSession = EventSession(sparkSession.sparkContext, DB_NAME)<jupyter_output><empty_output><jupyter_text>The next cell opens the database to allow operations against it to be executed.<jupyter_code>eventSession.open_database()<jupyter_output><empty_output><jupyter_text>With the following cells, we can list all existing tables and then load the table we previously created into the tab DataFrame reference. Note that we are defining the `tab` DataFrame reference that will be used later on in this notebook to create a temporary view.<jupyter_code>with EventContext.get_event_context(DB_NAME) as ctx:
   print("Event context successfully retrieved.")

print("Table names:")
table_names = ctx.get_names_of_tables()
for name in table_names:
   print(name)
tab = eventSession.load_event_table(TABLE_NAME)<jupyter_output><empty_output><jupyter_text>Let's recall the table schema we previously created.<jupyter_code>try:
    resolved_table_schema = ctx.get_table(TABLE_NAME)
    print(resolved_table_schema)
except Exception as err:
    print("Table not found")<jupyter_output><empty_output><jupyter_text>Best Practices for efficient queries In the next cell we create a lazily evaluated "view" that we can then use like a hive table in Spark SQL, but this is only evaluated when we actually run or cache query results. We are calling this view "readings" and that is how we will refer to it in the queries below:<jupyter_code>tab.createOrReplaceTempView("readings")
query = "SELECT count(*) FROM readings"
print("{}\nRunning query in Event Store...".format(query))
df_data = eventSession.sql(query)
df_data.toPandas()<jupyter_output><empty_output><jupyter_text>Let's have a look at the record structure<jupyter_code>query = "SELECT * FROM readings LIMIT 1"
print("{}\nRunning query in Event Store...".format(query))
df_data = eventSession.sql(query)
df_data.toPandas()
query = "SELECT MIN(ts), MAX(ts) FROM readings"
print("{}\nRunning query in Event Store...".format(query))
df_data = eventSession.sql(query)
df_data.toPandas()<jupyter_output><empty_output><jupyter_text>Optimal query through the index- Index queries will significantly reduce amount of data that needs to be scanned for results.     - Indexes in IBM Db2 Event Store are formed asynchronously to avoid insert latency.     - They are stored as a Log Structured Merge (LSM) Tree.    - The index is formed by "runs", which include sequences of sorted keys. These runs are written to disk during “Share” processing.    - These index runs are merged together over time to improve scan and I/O efficiency.- For an optimal query performance you must specify equality on all the equal_columns in the index and a range on the sort column in the index. For example, in the following query we are retrieving all the values in the range of dates for a specific device and sensor, where both the `deviceID` and `sensorID` are in the equal_columns definition for the index schema and the `ts` column is the sort column for the index. Then the following cell runs the query and caches the results. Note that this caching is for demostration purposes, to show the time it takes to run the query and cache the results in memory within Spark. This caching is recommended when you are going to do additional processing on this cached data, as the query against IBM Db2 Event Store is only run once.<jupyter_code>%%time
index_query = "SELECT ts, temperature  FROM readings where deviceID=1 and sensorID=12 and ts >1541021271619 and ts < 1541043671128 order by ts"
print("{}\nCreating a dataframe for the query ...".format(index_query))
df_index_query = eventSession.sql(index_query)
df_index_query.cache()<jupyter_output><empty_output><jupyter_text>Finally the results from the cached data can be visualized:<jupyter_code>df_index_query.toPandas()<jupyter_output><empty_output><jupyter_text>Sub-optimal queryThis next query shows a sub-optimal query that only specifies equality in one of the equal_columns in the index schema, and for this reason ends up doing a full scan of the table.<jupyter_code>%%time
fullscan_query = "SELECT count(*) FROM readings where sensorID = 7"
print("{}\nCreating a dataframe for the query...".format(fullscan_query))
df_fullscan_query = eventSession.sql(fullscan_query)
df_fullscan_query.cache()
df_fullscan_query.toPandas()<jupyter_output><empty_output><jupyter_text>Accessing multiple sensorIDs optimallyThe easiest way to write a query that needs to retrieve multiple values in the equal_columns in the index schema is by using an *In-List*. With this, you can get optimal index access across multiple sensorID's. In this example we specify equality for a specific deviceID, and an In-List for the four sensors we are trying to retrieve. To limit the number of records we are returning we also include a range of timestamps.<jupyter_code>%%time
inlist_query = "SELECT deviceID, sensorID, ts FROM readings where deviceID=1 and sensorID in (1, 5, 7, 12) and ts >1541021271619 and ts < 1541043671128 order by ts"
print("{}\nCreating a dataframe for the query...".format(inlist_query))
df_inlist_query = eventSession.sql(inlist_query)
df_inlist_query.cache()
df_inlist_query.toPandas()<jupyter_output><empty_output><jupyter_text>Exploiting the synopsis table:  Advanced medium weight queries- Event Store tables include a synopsis table which summarizes the minimum/maximum values of the data for each range of rows in the synopsis table.      - It contains one range for every 1000 rows.     - It is stored in a separate internal table in the shared storage layer.     - It is parquet compressed to minimize footprint.     - For highly selective queries, it can improve performance by up to 1000x.- Using an equality or a range predicate on a clustered field (e.g. the 'ts' column in our case because the data is inserted into the table in order) is faster than doing a full scan as it should be able to exploit the synopsis table, but this will be slower than an optimal index scan.<jupyter_code>%%time
synopsis_query = "SELECT deviceID, sensorID, ts FROM readings where ts >1541021271619 and ts < 1541043671128 order by ts"
print("{}\nCreating a dataframe for the query...".format(synopsis_query))
df_synopsis_query = eventSession.sql(synopsis_query)
df_synopsis_query.cache()
df_synopsis_query.toPandas()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06952333909607311, 'fraction_numerical': 0.01506544825882934, 'mean_word_length': 5.671334431630972, 'pattern_counts': {'":': 1, '<': 60, '<?xml version=': 0, '>': 60, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '89493', 'license': 'Apache-2.0', 'n_tokens_mistral': 2382, 'n_tokens_neox': 2230, 'n_words': 976, 'path': 'notebooks/Event_Store_Querying.ipynb', 'repo_name': 'markstur/db2-event-store-iot-analytics', 'size': '8098'}