<jupyter_start><jupyter_text>Using Sql Magic Function with ClickHouseThis notebook goes from nothing to a nice graph in three steps.  We'll extract data using the %sql magic function.  You'll need to have created the iris table for this to work.  Run the EX-1-Getting-to-Know-the-Clickhouse-driver-Client.ipynb notebook to load data into the iris table if you have not already done so.  First import SQLAlchemy and activate the %sql function.  This just needs to be done once.<jupyter_code>from sqlalchemy import create_engine
%load_ext sql<jupyter_output><empty_output><jupyter_text>Connect to a local server using native protocol, select data from the iris table, and pop it into a data frame. You should see 150 rows in the data frame.<jupyter_code>%sql clickhouse://default:@localhost/default
result = %sql SELECT * FROM iris
df = result.DataFrame()
df.tail()<jupyter_output>* clickhouse://default:***@localhost/default
Done.<jupyter_text>Everybody likes a nice picture.  Let's make a scatter graph that shows data with different markers for each species of iris.  We'll use standard pandas tricks to find the species names and query for each one of the.  Pro tip:  For large datasets you could get unique names using SQL. We'll just cheat and use the pandas unique() method on the data frame.<jupyter_code>import matplotlib.pyplot as plt
%matplotlib inline

# Break up the data frame and graph each species separately. 
species = df.species.unique()
markers = ['o', 'x', '^']
for i in range(3):
    df_segment = df[df['species'] == species[i]]
    plt.scatter('sepal_length', 
                'petal_length', 
                data=df_segment, 
                marker=markers[i],
                label=species[i])
# Add proper labels and show the result. 
plt.xlabel('sepal length [cm]')
plt.ylabel('petal length [cm]')
plt.legend(loc='upper left')
plt.show()<jupyter_output><empty_output><jupyter_text>From this we can see that iris-setosa is linearly separable from the other two species.  This is significant for running machine learning. One final note is that when you don't know what's coming back it's good to describe the data set to ensure the values you think are numeric actually are. For example ClickHouse Decimal values don't automatically coerce to floats which means data science stack operations won't work as expected.<jupyter_code>import pandas
df.describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07714641227706345, 'fraction_numerical': 0.002073828287017835, 'mean_word_length': 4.457013574660634, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 16, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '178384', 'license': 'MIT', 'n_tokens_mistral': 712, 'n_tokens_neox': 665, 'n_words': 297, 'path': 'notebooks/EX-3-Sql-Magic-Functions.ipynb', 'repo_name': 'hodgesrm/clickhouse-python-examples', 'size': '2411'}