def main(args):
ibmcloud_apikey = args.get("apikey", "")
if ibmcloud_apikey == "":
return {'error': 'No API key specified'}
sql_instance_crn = args.get("sqlquery_instance_crn", "")
if sql_instance_crn == "":
return {'error': 'No SQL Query instance CRN specified'}
target_url = args.get("target_url", "")
if target_url == "":
return {'error': 'No Cloud Object Storage target URL specified'}
client_information = args.get("client_info", "ibmcloudsql cloud function")
sql_statement_text = args.get("sql", "")
if sql_statement_text == "":
return {'error': 'No SQL statement specified'}
sqlClient = ibmcloudsql.SQLQuery(ibmcloud_apikey, sql_instance_crn, target_url, client_info=client_information)
sqlClient.logon()
jobId = sqlClient.submit_sql(sql_statement_text)
sqlClient.wait_for_job(jobId)
result = sqlClient.get_result(jobId)
result_location = sqlClient.get_job(jobId)['resultset_location']
access_code = 'import ibmcloudsql\n'
access_code += 'sqlClient = ibmcloudsql.SQLQuery(' + ibmcloud_apikey + ', ' + sql_instance_crn + ', ' + target_url + ')\n' +
access_code += 'sqlClient.logon()\n'
access_code += 'result_df = sqlClient.get_result(' + jobId + ')\n'
return {'jobId': jobId, 'result_location': result_location, 'result_access_pandas': access_code, 'result_set_sample': result.head(10).to_json(orient='table')}
def main(dict):
my_bluemix_apikey = 'YNiXREBMTfQsSzKrXYhQJElNXnUFEgpQ7qVVTkDK3_Zr'
my_instance_crn = 'crn%3Av1%3Abluemix%3Apublic%3Asql-query%3Aus-south%3Aa%2Fd86af7367f70fba4f306d3c19c938f2f%3Ad1b2c005-e3d8-48c0-9247-e9726a7ed510%3A%3A'
my_target_cos_endpoint = 's3.us-south.objectstorage.softlayer.net'
my_target_cos_bucket = 'sqltempregional'
sqlClient = ibmcloudsql.SQLQuery(
my_bluemix_apikey,
my_instance_crn,
my_target_cos_endpoint,
my_target_cos_bucket,
client_info='ibmcloudsql cloud function test')
sqlClient.logon()
jobId = sqlClient.submit_sql("WITH orders_shipped AS \
(SELECT OrderID, EmployeeID, (CASE WHEN shippedDate < requiredDate \
THEN 'On Time' \
ELSE 'Late' \
END) AS Shipped \
FROM cos://us-geo/sql/orders.parquet STORED AS PARQUET) \
SELECT e.FirstName, e.LastName, COUNT(o.OrderID) As NumOrders, Shipped \
FROM orders_shipped o, \
cos://us-geo/sql/employees.parquet STORED AS PARQUET e \
WHERE e.EmployeeID = o.EmployeeID \
GROUP BY e.FirstName, e.LastName, Shipped \
ORDER BY e.LastName, e.FirstName, NumOrders DESC")
sqlClient.wait_for_job(jobId)
result = sqlClient.get_result(jobId)
return {'jobId': jobId, 'result_set': result.to_json(orient='table')}
def main(args):
ibmcloud_apikey = args.get("apikey", "")
if ibmcloud_apikey == "":
return {'error': 'No API key specified'}
sql_instance_crn = args.get("sqlquery_instance_crn", "")
if sql_instance_crn == "":
return {'error': 'No Data Engine instance CRN specified'}
target_url = args.get("target_url", "")
client_information = args.get("client_info", "ibmcloudsql cloud function")
sql_statement_text = args.get("sql", "")
if sql_statement_text == "":
return {'error': 'No SQL statement specified'}
sqlClient = ibmcloudsql.SQLQuery(ibmcloud_apikey,
sql_instance_crn,
target_url,
client_info=client_information)
sqlClient.logon()
try:
jobId = sqlClient.submit_sql(sql_statement_text)
except Exception as e:
return {'Error': e}
jobDetails = sqlClient.get_job(jobId)
access_code = 'import ibmcloudsql\n'
access_code += 'api_key="" # ADD YOUR API KEY HERE\n'
access_code += 'sqlClient = ibmcloudsql.SQLQuery(api_key, ' + sql_instance_crn + ', ' + target_url + ')\n'
access_code += 'sqlClient.logon()\n'
access_code += 'result_df = sqlClient.get_result(' + jobId + ')\n'
return {
'jobId': jobId,
'result_location': jobDetails['resultset_location'],
'job_status': jobDetails['status'],
'result_access_pandas': access_code
}
sys.path.append('ibmcloudsql')
import ibmcloudsql
try:
from exceptions import RateLimitedException
except Exception:
from .exceptions import RateLimitedException
import test_credentials
import pandas as pd
pd.set_option('display.max_colwidth', None)
pd.set_option('display.max_columns', 20)
if test_credentials.result_location[-1] != "/":
test_credentials.result_location += "/"
sqlClient = ibmcloudsql.SQLQuery(test_credentials.apikey, test_credentials.instance_crn, target_cos_url=test_credentials.result_location, client_info='ibmcloudsql test')
sqlClient.logon()
sqlClient.sql_ui_link()
print("Running test with individual method invocation and Parquet target:")
jobId = sqlClient.submit_sql("SELECT * FROM cos://us-geo/sql/employees.parquet STORED AS PARQUET LIMIT 10 INTO {} STORED AS PARQUET".format(test_credentials.result_location))
sqlClient.wait_for_job(jobId)
result_df = sqlClient.get_result(jobId)
print("jobId {} restults are stored in {}. Result set is:".format(jobId, sqlClient.get_job(jobId)['resultset_location']))
print(result_df.head(200))
print("Running test with individual method invocation and ORC target:")
jobId = sqlClient.submit_sql("SELECT * FROM cos://us-geo/sql/employees.parquet STORED AS PARQUET LIMIT 10 INTO {} STORED AS ORC".format(test_credentials.result_location))
sqlClient.wait_for_job(jobId)
try:
result_df = sqlClient.get_result(jobId)
if ibmcloud_apikey == "":
print({'error': 'No API key specified'})
quit()
if sql_instance_crn == "":
print({'error': 'No Data Engine instance CRN specified'})
quit()
if sql_statement_text == "":
if sql_job_id == "":
print({'error': 'Neither SQL statement nor job id specified'})
quit()
if sql_index == "":
print({'info': 'No starting index specified. Will return starting with first row'})
if sql_max_results == "":
print({'info': 'No max results specified. Will return all results'})
sqlClient = ibmcloudsql.SQLQuery(ibmcloud_apikey, sql_instance_crn, target_url, client_info=client_information)
sqlClient.logon()
next_index = ""
if sql_job_id == "":
jobId = sqlClient.submit_sql(sql_statement_text)
if not sql_async_execution:
sqlClient.wait_for_job(jobId)
if sql_max_results == "":
result = sqlClient.get_result(jobId)
else:
result = sqlClient.get_result(jobId).iloc[0:sql_max_results]
if len(sqlClient.get_result(jobId).index) > sql_max_results: next_index = sql_max_results
else:
first_index = sql_index
last_index = first_index+sql_max_results
result = sqlClient.get_result(sql_job_id).iloc[first_index:last_index]
import test_credentials # noqa
try:
from exceptions import RateLimitedException
except Exception:
from .exceptions import RateLimitedException
pd.set_option("display.max_colwidth", None)
pd.set_option("display.max_columns", 20)
if test_credentials.result_location[-1] != "/":
test_credentials.result_location += "/"
sqlClient = ibmcloudsql.SQLQuery(
test_credentials.apikey,
test_credentials.instance_crn,
client_info="ibmcloudsql test",
) # maintain backward compatible
sqlClient = ibmcloudsql.SQLQuery(
test_credentials.apikey,
test_credentials.instance_crn,
target_cos_url=test_credentials.result_location,
client_info="ibmcloudsql test",
)
sqlClient.logon()
sqlClient.sql_ui_link()
print("Running test with individual method invocation and Parquet target:")
jobId = sqlClient.submit_sql(
"SELECT * FROM cos://us-geo/sql/employees.parquet STORED AS PARQUET LIMIT 10 INTO {} STORED AS PARQUET"
import sys
sys.path.append('ibmcloudsql')
import ibmcloudsql
import test_credentials
import pandas as pd
pd.set_option('display.max_colwidth', -1)
sqlClient = ibmcloudsql.SQLQuery(test_credentials.apikey,
test_credentials.instance_crn,
client_info='ibmcloudsql test')
sqlClient.logon()
print("Running test with individual method invocation and Parquet target:")
jobId = sqlClient.submit_sql(
"SELECT * FROM cos://us-geo/sql/employees.parquet STORED AS PARQUET LIMIT 10 INTO {} STORED AS PARQUET"
.format(test_credentials.result_location))
sqlClient.wait_for_job(jobId)
result_df = sqlClient.get_result(jobId)
print("jobId {} restults are stored in {}. Result set is:".format(
jobId,
sqlClient.get_job(jobId)['resultset_location']))
print(result_df.head(200))
print("Running test with individual method invocation and ORC target:")
jobId = sqlClient.submit_sql(
"SELECT * FROM cos://us-geo/sql/employees.parquet STORED AS PARQUET LIMIT 10 INTO {} STORED AS ORC"
.format(test_credentials.result_location))
sqlClient.wait_for_job(jobId)
try:
result_df = sqlClient.get_result(jobId)
except ValueError as e: