<jupyter_start><jupyter_text>ibm_sql_query Execute arbitrary SQL queries against CSV and PARQUET files using IBM Cloud SQL Query and Cloud Object Store<jupyter_code>!pip install nbformat==5.1.3 ibmcloudsql==0.4.29
import glob
import logging
import ibmcloudsql
from ibmcloudsql import SQLQuery
import os
import shutil
import sys
import re
# IBM Cloud API key (alternative to token)
api_key = os.environ.get('api_key')

# IBM Cloud Token (alternative to API key)
token = os.environ.get('token')

# (unique) Custom Resource Name (CRN) of IBM SQL Query Service
sql_query_crn = os.environ.get('sql_query_crn')

# URI of resulting file (example: cos://s3.eu-de.cloud-object-storage.appdomain.cloud/cos-rkie-sqlquery-test/result)
output_uri = os.environ.get('output_uri')

# default: CSV - (will be generated into according STORED AS … clause in the INTO clause)
out_format = os.environ.get('out_format' , 'CSV')

# if set - will be generated into according PARTITIONED BY (<columns>) clause in the INTO clause)
out_partition_columns = os.environ.get('out_partition_columns')

# will be generated into according PARTITIONED INTO <num> OBJECTS clause in INTO clause
out_number_of_objects = os.environ.get('out_number_of_objects')

# will be generated into according PARTITIONED EVERY <num> ROWS clause in INTO clause
out_rows_per_object = os.environ.get('out_rows_per_object')

# default: False - only valid when no partitioning option is specified. Will be generated into sqlClient.rename_exact_result(jobid) after SQL has run.
out_exact_name = bool(os.environ.get('out_exact_name', False))

# default: False - will be generated into JOBPREFIX NONE in the INTO clause. Will cause results of previous runs with same output_uri to be overwritten, because no unique sub folder will be created for the result)
out_no_jobid_folder = bool(os.environ.get('out_no_jobid_folder', False))

# sql statement to execute ()
sql = os.environ.get('sql')
parameters = list(
    map(lambda s: re.sub('$', '"', s),
        map(
            lambda s: s.replace('=', '="'),
            filter(
                lambda s: s.find('=') > -1 and bool(re.match(r'[A-Za-z0-9_]*=[.\/A-Za-z0-9]*', s)),
                sys.argv
            )
    )))

out_exact_name = bool(out_exact_name)
out_no_jobid_folder = bool(out_no_jobid_folder)


for parameter in parameters:
    logging.warning('Parameter: ' + parameter)
    exec(parameter)
if token is None or len(api_key) > 0:
    sqlClient = SQLQuery(api_key, sql_query_crn)
else:
    sqlClient = SQLQuery(api_key=None, token=token, instance_crn=sql_query_crn)

sql = sql + ' INTO {}'.format(output_uri)

partitioned_by = False

if out_partition_columns is not None and len(out_partition_columns) > 0:
    if not partitioned_by:
        sql = sql + ' PARTITIONED'
        partitioned_by = True
    sql = sql + ' BY ({})'.format(out_partition_columns)

if out_number_of_objects is not None and len(out_number_of_objects) > 0:
    if not partitioned_by:
        sql = sql + ' PARTITIONED'
        partitioned_by = True
    sql = sql + ' INTO {} OBJECTS'.format(out_number_of_objects)    

if out_rows_per_object is not None and len(out_rows_per_object) > 0:
    if not partitioned_by:
        sql = sql + ' PARTITIONED'
        partitioned_by = True
    sql = sql + ' EVERY {} ROWS'.format(out_rows_per_object)

if out_no_jobid_folder:
    sql = sql + ' JOBPREFIX NONE'
    
sql = sql + ' STORED AS {}'.format(out_format)

if out_exact_name:
    job_id = sqlClient.submit_sql(sql)
    job_status = sqlClient.wait_for_job(job_id)
    print("Job " + job_id + " terminated with status: " + job_status)
    sqlClient.rename_exact_result(job_id) 
else:
    sqlClient.run_sql(sql)
print(sql)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10341122750470051, 'fraction_numerical': 0.0045662100456621, 'mean_word_length': 4.0943912448700415, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 13, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '106593', 'license': 'Apache-2.0', 'n_tokens_mistral': 1260, 'n_tokens_neox': 1244, 'n_words': 347, 'path': 'component-library/transform/ibm-sql-query.ipynb', 'repo_name': 'j622amilah/-claimed', 'size': '3723'}