<jupyter_start><jupyter_text>+![SQLAlchemy](https://www.sqlalchemy.org/img/sqla_logo.png)  Getting started with SQLAlchemy HeavyAI**HeavyDB** is the world's fastest open source **SQL** engine,equally powerful at the heart of the HeavyAI platform as it is acceleratingthird-party analytic apps. It optimizes the memory and computes layers to deliver unprecedented performance. **HeavyDB** was designed to keep hot data in **GPU** memory for thefastest access possible. Other **GPU** database systems have taken the approach of storing the data in **CPU** memory, only moving it to **GPU** at query time, trading the gains they receive from **GPU** parallelism with transfer overheads over the **PCIe** bus.**HeavyDB** avoids this transfer inefficiency by caching the most recentlytouched data in High Bandwidth Memory on the **GPU**, which offers up to 10x the bandwidth of **CPU DRAM** and far lower latency. **HeavyDB** is alsodesigned to exploit efficient inter-GPU communication infrastructure such as **NVIDIA NVLink** when available.For data manipulation, HeavyAI provides **heavyai**/**pyheavydb** but,maybe you would like to use a more common and high level tool for your data workflow such as[Ibis](https://github.com/ibis-project/ibis) or [SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy). The great news is thatHeavyAI also provides a backend/dialect for both!If you want more information about `ibis-heavyai` check its[repository](https://github.com/heavyai/ibis-heavyai)This tutorial is about the first steps with **SQLAlchemy HeavyAI**! Installation**sqlalchemy-heavyai** is available on **PyPI** and **conda-forge** and you caninstall it using one of the following commands:```bash if you are a conda user (also work with mamba)$ conda install -y sqlalchemy-heavyai```or```bash if you are a pip user$ pip install sqlalchemy-heavyai````sqlalchemy-heavyai` is a `sqlalchemy` dialect, so you don't need to import`sqlalchemy-heavyai` directly. Just import `sqlalchemy` and create a connectionusing the following structure:`heavydb://:@:/?protocol=`<jupyter_code>import sqlalchemy
from sqlalchemy import create_engine
import pandas as pd

sqlalchemy.__version__
engine = create_engine(
    "heavydb://admin:HyperInteractive@localhost:6274/heavyai?protocol=binary"
)

con = engine.connect()<jupyter_output><empty_output><jupyter_text>As an example, let's work on the first table we can find in our database.<jupyter_code>table_name = con.execute(f"SHOW TABLES").first()[0]
print(table_name)<jupyter_output>lidar<jupyter_text>For this first tutorial, we are going to use a table called `github`.<jupyter_code>metadata = sqlalchemy.MetaData()

lidar = sqlalchemy.Table(
    f"{table_name}", 
    metadata, 
    autoload=True, 
    autoload_with=engine
)<jupyter_output><empty_output><jupyter_text>And, inspecting the variable `github` it seems it is working!<jupyter_code>lidar<jupyter_output><empty_output><jupyter_text>Now, let's try a simple query using `sqlalchemy` API:<jupyter_code>query = sqlalchemy.select([f"{table_name}"]).limit(1)
str(query.compile())
results = con.execute(query).fetchall()
results<jupyter_output>/var/folders/5x/5blbwvrs6l5_wct5ncsp5f_m0000gn/T/ipykernel_46209/272572304.py:1: SAWarning: Dialect heavydb:heavydb will not make use of SQL compilation caching as it does not set the 'supports_statement_cache' attribute to ``True``.  This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions.  Dialect maintainers should seek to set this attribute to True after appropriate development and testing for SQLAlchemy 1.4 caching support.   Alternatively, this attribute may be set to False which will disable this warning. (Background on this error at: https://sqlalche.me/e/14/cprf)
  results = con.execute(query).fetchall()<jupyter_text>We can also use **Pandas** to manipulate this result!<jupyter_code>df = pd.DataFrame(results)
df.columns = results[0].keys()
df<jupyter_output><empty_output><jupyter_text>Also, if you are familiar with **SQL** you it directly:<jupyter_code>results = con.execute(f"SELECT * FROM {table_name} LIMIT 1").fetchall()
results<jupyter_output><empty_output><jupyter_text>Filtering The **sqlalchemy** API is very similar to **SQL** structure. For example,to execute a SQL `SELECT` you can use `sqalchemy.select` that returns an objectthat allows other **SQL** clauses, such as `limit`, `where`, etc.Let's try to filter our dataset with the first 10 records with `type PushEvent`.<jupyter_code>query = (
    sqla.select([f"{table_name}"])
    .where(github.columns.type=="PushEvent").limit(10)
)<jupyter_output><empty_output><jupyter_text>And it will be translated to **SQL**:<jupyter_code>print(query.compile())<jupyter_output>SELECT lidar.x, lidar.y, lidar.z 
FROM lidar
 LIMIT :param_1<jupyter_text>As you can see in the output above, it uses some "variables" instead of the real values. If you want see the real **SQL**, use the following command:<jupyter_code>print(query.compile(engine, compile_kwargs={"literal_binds": True}))<jupyter_output>SELECT lidar.x, lidar.y, lidar.z 
FROM lidar 
 LIMIT 1<jupyter_text>Using with PandasIf you managed to read until here, probably you also have already heard about [Pandas](https://pandas.pydata.org/),the most popular **Data Frame** library for **Python**. As **sqlalchemy-heavyai** is a **sqlalchemy** dialect,you also can use it directly with **Pandas**:<jupyter_code>pd.read_sql(query, engine)<jupyter_output><empty_output><jupyter_text>Or, if you want to use **SQL** directly:<jupyter_code>sql = f"SELECT * FROM {table_name} LIMIT 1"
pd.read_sql(sql, engine)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10653284028878324, 'fraction_numerical': 0.008100017608733932, 'mean_word_length': 6.493403693931398, 'pattern_counts': {'":': 1, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 6, 'lorem ipsum': 0, 'www.': 1, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '663408', 'license': 'MIT', 'n_tokens_mistral': 1832, 'n_tokens_neox': 1719, 'n_words': 579, 'path': 'docs/tutorials/Introduction.ipynb', 'repo_name': 'heavyai/sqlalchemy-heavyai', 'size': '5679'}