Extending IBM Big SQL

You can extend the capability of Big SQL by adding certain JAR files.

About this task

You can use Java™ packages that are available from the web, or Java packages that you create to extend the functions of Big SQL. You extend Big SQL by adding JAR files to the appropriate paths. Big SQL includes a useful custom SerDe (Serializer/Deserializer) extension for processing JSON data, which can be used to transform a JSON record into something that Big SQL can process. To use this SerDe, specify the ROW FORMAT SERDE option in the CREATE TABLE syntax. For example:

CREATE EXTERNAL HADOOP TABLE bigsql.example_json (
 col1 int
 col2 date
) ROW FORMAT SERDE 'com.ibm.biginsights.bigsql.serde.JSONSerDe';

In addition to this custom JSONSerDe, which has been provided already, you can add or create your own custom SerDe or download it from the web.

When you add a JAR file to Big SQL, the JAR file is available to the Big SQL JVM and the MapReduce JVMs.

Procedure

Stop the Big SQL server.

Depending on the purpose of the JAR file, do the following steps:

Description of the JAR file Path to the appropriate library

If the JAR file contains a custom SerDe or a User Defined Function (UDF)

Description of the JAR file	Path to the appropriate library
If the JAR file contains a custom SerDe or a User Defined Function (UDF)	Create or download the appropriate SerDe JAR file or a user-defined function (UDF) JAR file. If they do not already exist, create directories under biginsights/bigsql to contain SerDe and UDF JAR files: `hdfs dfs -mkdir /biginsights/bigsql/serde hdfs dfs -mkdir /biginsights/bigsql/udf` Make sure the security permissions for the distributed file system (DFS) /biginsights/bigsql directory allow the appropriate write access. If required, do the following steps to change the permission: From a Linux® terminal window, log in as the root user and type the password when prompted: `su root` Switch to the hdfs user account: `su hdfs` Run the following commands to change the directory permissions: `hdfs dfs -chmod 777 /biginsights/bigsql/serde hdfs dfs -chmod 777 /biginsights/bigsql/udf` Verify the permissions on the directory: `hdfs dfs -ls /biginsights` Exit the hdfs account and then the root account by typing `exit` in each account shell. Copy the JAR file from your local file system to the distributed file system directory. The JAR files for user applications such as custom SerDes or UDFs are stored in DFS in the following paths: `hdfs dfs -copyFromLocal /yourLocaldir/hive-json-serde-0.2.jar /biginsights/bigsql/serde` `hdfs dfs -copyFromLocal /yourLocaldir/my_UDFfile.jar /biginsights/bigsql/udf` Only a member of the hadoop group can put JAR files to this location. Set the $HADOOP_HOME environment variable to the appropriate Hadoop client directory so that you can run the required stored procedure. If you accepted the installation defaults, the directory is /usr/hdp/current/hadoop-client. From the bash shell, run the following command: `export HADOOP_HOME=/usr/hdp/current/hadoop-client` Restart the Big SQL server. Launch your Big SQL query execution tool, such as JSQSH or Data Server Manager, and connect to the Big SQL database as the bigsql user. Call the Big SQL stored procedure to complete the SerDe registration: `call SYSHADOOP.SERDE_JAR_SYNC();` The stored procedure copies the JAR file into the appropriate installation directories on your cluster and adds it to your class path.
If you want to add a JDBC driver of a source database so that it can be used in the Big SQL LOAD USING statement in the WITH PARAMETERS option:	Copy the JAR file to /usr/hdp/<`version_number`>/sqoop/lib. Restart the Big SQL server.

Create or download the appropriate SerDe JAR file or a user-defined function (UDF) JAR file.
If they do not already exist, create directories under biginsights/bigsql to contain SerDe and UDF JAR files:
```
hdfs dfs -mkdir /biginsights/bigsql/serde
hdfs dfs -mkdir /biginsights/bigsql/udf
```
Make sure the security permissions for the distributed file system (DFS) /biginsights/bigsql directory allow the appropriate write access. If required, do the following steps to change the permission:
1. From a Linux® terminal window, log in as the root user and type the password when prompted:
```
su root
```
2. Switch to the hdfs user account:
```
su hdfs
```
3. Run the following commands to change the directory permissions:
```
hdfs dfs -chmod 777 /biginsights/bigsql/serde
hdfs dfs -chmod 777 /biginsights/bigsql/udf
```
4. Verify the permissions on the directory:
```
hdfs dfs -ls /biginsights
```
5. Exit the hdfs account and then the root account by typing exit in each account shell.
Copy the JAR file from your local file system to the distributed file system directory. The JAR files for user applications such as custom SerDes or UDFs are stored in DFS in the following paths:
```
hdfs dfs -copyFromLocal /yourLocaldir/hive-json-serde-0.2.jar /biginsights/bigsql/serde
```
```
hdfs dfs -copyFromLocal /yourLocaldir/my_UDFfile.jar /biginsights/bigsql/udf
```
Only a member of the hadoop group can put JAR files to this location.
Set the $HADOOP_HOME environment variable to the appropriate Hadoop client directory so that you can run the required stored procedure. If you accepted the installation defaults, the directory is /usr/hdp/current/hadoop-client. From the bash shell, run the following command:
```
export HADOOP_HOME=/usr/hdp/current/hadoop-client 
```
Restart the Big SQL server. Launch your Big SQL query execution tool, such as JSQSH or Data Server Manager, and connect to the Big SQL database as the bigsql user.
Call the Big SQL stored procedure to complete the SerDe registration:
```
call SYSHADOOP.SERDE_JAR_SYNC();
```
The stored procedure copies the JAR file into the appropriate installation directories on your cluster and adds it to your class path.

If you want to add a JDBC driver of a source database so that it can be used in the Big SQL LOAD USING statement in the WITH PARAMETERS option:

Copy the JAR file to /usr/hdp/<version_number>/sqoop/lib.
Restart the Big SQL server.