Connection details



In [ ]:

    
# Cluster number, e.g. 10000
cluster  = ''

# Cluster username for BigSQL
username = ''

# Cluster password for BigSQL
password = ''

# BigSQL host number, e.g. 2
bigsqlhost = '2'

# Bigsql table name, e.g. MYSCHEMA.MYTABLE
bigsqltable = ''

Cluster URLs



In [ ]:

    
import os
cwd = os.getcwd()

cls_host = 'ehaasp-{0}-mastermanager.bi.services.bluemix.net'.format(cluster)
sql_host = 'ehaasp-{0}-master-{1}.bi.services.bluemix.net'.format(cluster, bigsqlhost)

Use openssl on the spark service to get the cluster certificate



In [ ]:

    
!openssl s_client -showcerts -connect {cls_host}:9443 < /dev/null | openssl x509 -outform PEM > certificate
!rm -f truststore.jks
!keytool -import -trustcacerts -alias biginsights -file certificate -keystore truststore.jks -storepass mypassword -noprompt

Now attempt to connect to BigInsights on Cloud and print a count of rows in the bigsqltable



In [ ]:

    
url  = 'jdbc:db2://{0}:51000/bigsql:user={1};password={2};sslConnection=true;' \
       'sslTrustStoreLocation={3}/truststore.jks;Password=mypassword;'.format(sql_host, username, password, cwd)

df = sqlContext.read.format('jdbc').options(
            url=url, 
            driver='com.ibm.db2.jcc.DB2Driver', 
            dbtable=bigsqltable
        ).load()

print(df.count())