<jupyter_start><jupyter_text>Querying Multiple Data Formats In this demo we join a CSV, a Parquet File, and a GPU DataFrame(GDF) in a single query using BlazingSQL.In this notebook, we will cover: - How to set up [BlazingSQL](https://blazingsql.com) and the [RAPIDS AI](https://rapids.ai/) suite.- How to create and then join BlazingSQL tables from CSV, Parquet, and GPU DataFrame (GDF) sources. ![Impression](https://www.google-analytics.com/collect?v=1&tid=UA-39814657-5&cid=555&t=event&ec=guides&ea=federated_query_demo&dt=federated_query_demo)  Setup Environment Sanity Check RAPIDS packages (BlazingSQL included) require Pascal+ architecture to run. For Colab, this translates to a T4 GPU instance. The cell below will let you know what type of GPU you've been allocated, and how to proceed.<jupyter_code>!wget https://github.com/BlazingDB/bsql-demos/raw/master/utils/colab_env.py
!python colab_env.py<jupyter_output>***********************************
GPU = b'Tesla T4'
Woo! You got the right kind of GPU!
***********************************<jupyter_text>Installs The cell below pulls our Google Colab install script from the `bsql-demos` repo then runs it. The script first installs miniconda, then uses miniconda to install BlazingSQL and RAPIDS AI. This takes a few minutes to run.<jupyter_code>!wget https://github.com/BlazingDB/bsql-demos/raw/master/utils/bsql-colab.sh 
!bash bsql-colab.sh

import sys, os, time
sys.path.append('/usr/local/lib/python3.6/site-packages/')
os.environ['NUMBAPRO_NVVM'] = '/usr/local/cuda/nvvm/lib64/libnvvm.so'
os.environ['NUMBAPRO_LIBDEVICE'] = '/usr/local/cuda/nvvm/libdevice/'

import subprocess
subprocess.Popen(['blazingsql-orchestrator', '9100', '8889', '127.0.0.1', '8890'],stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
subprocess.Popen(['java', '-jar', '/usr/local/lib/blazingsql-algebra.jar', '-p', '8890'])

import pyblazing.apiv2.context as cont
cont.runRal()
time.sleep(1)<jupyter_output><empty_output><jupyter_text>Grab DataThe data is in our public S3 bucket so we can use wget to grab it<jupyter_code>!wget 'https://blazingsql-colab.s3.amazonaws.com/cancer_data/cancer_data_00.csv'
!wget 'https://blazingsql-colab.s3.amazonaws.com/cancer_data/cancer_data_01.parquet'
!wget 'https://blazingsql-colab.s3.amazonaws.com/cancer_data/cancer_data_02.csv'<jupyter_output><empty_output><jupyter_text>Import packages and create Blazing ContextYou can think of the BlazingContext much like a Spark Context (i.e. where information such as FileSystems you have registered and Tables you have created will be stored). If you have issues running this cell, restart runtime and try running it again.<jupyter_code># Import RAPIDS AI stack
from blazingsql import BlazingContext
import cudf

bc = BlazingContext()<jupyter_output>BlazingContext ready<jupyter_text>Create Table from CSVHere we create a BlazingSQL table directly from a comma-separated values (CSV) file.<jupyter_code># define column names and types
column_names = ['diagnosis_result', 'radius', 'texture', 'perimeter']
column_types = ['float32', 'float32', 'float32', 'float32']

# create table from CSV file
bc.create_table('data_00', '/content/cancer_data_00.csv', delimiter=',', dtype=column_types, names=column_names)<jupyter_output><empty_output><jupyter_text>Create Table from ParquetHere we create a BlazingSQL table directly from an Apache Parquet file.<jupyter_code># create table from Parquet file
bc.create_table('data_01', '/content/cancer_data_01.parquet')<jupyter_output><empty_output><jupyter_text>Create Table from GPU DataFrameHere we use cuDF to create a GPU DataFrame (GDF), then use BlazingSQL to create a table from that GDF.The GDF is the standard memory representation for the RAPIDS AI ecosystem.<jupyter_code># define column names and types
column_names = ['compactness', 'symmetry', 'fractal_dimension']
column_types = ['float32', 'float32', 'float32', 'float32']

# make GDF with cuDF
gdf_02 = cudf.read_csv('/content/cancer_data_02.csv',delimiter=',', dtype=column_types, names=column_names)

# create BlazingSQL table from GDF
bc.create_table('data_02', gdf_02)<jupyter_output><empty_output><jupyter_text>Join Tables Together Now we can use BlazingSQL to join all three data formats in a single federated query.<jupyter_code># define a query
sql = '''
        SELECT a.*, b.area, b.smoothness, c.* from main.data_00 as a
        LEFT JOIN main.data_01  as b
        ON (a.perimeter = b.perimeter)
        LEFT JOIN main.data_02 as c
        ON (b.compactness = c.compactness)
      '''

# join the tables together
join = bc.sql(sql).get()

# extract dataframe
result = join.columns

# display results
result<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12223168654173765, 'fraction_numerical': 0.019378194207836458, 'mean_word_length': 5.817126269956459, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 31, 'https://': 8, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 1, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '234925', 'license': 'Apache-2.0', 'n_tokens_mistral': 1642, 'n_tokens_neox': 1546, 'n_words': 444, 'path': 'colab_notebooks/federated_query_demo.ipynb', 'repo_name': 'gumdropsteve/bsql-demos', 'size': '4696'}