Manage the Spark SQL Connector

Describes the Spark SQL connector.

The Self-Service Analytics Spark SQL connector lets you access the data available in Spark SQL databases using the Self-Service Analytics client. The Self-Service Analytics Spark SQL connector supports Spark SQL versions 2.3 through 3.0.

Before you can establish a connection from Self-Service Analytics to Spark SQL storage, a connector server needs to be installed and configured. See Manage Connectors and Connector Servers for general instructions and Connect to Spark SQL for details specific to the Spark SQL connector.

After setting up the connector, create data sources that specify the necessary connection information and identify the data you want to use. See Create and Manage Data Sources for more information. After you set up your data sources, create dashboards, self service reports, and visuals from the data in these data sources.

Feature Support

Connector support for specific features is shown in the following table.

Key: Y - Supported; N - Not Supported; N/A - not applicable

Feature	Supported?			Notes
Admin-Defined Functions	Y
Box Plots	Y
Custom SQL Queries	Y			If you need to access a BigQuery partition, explicitly include an alias for the built in partition column in your select clause, such as select *, _PARTITIONTIME as pt from projectId.datasetId.tableId.
Derived Fields (Row-Level Expressions)	Y
Distinct Counts	Y
Fast Distinct Values	N/A
Group By Multiple Fields	Y
Group By Time	Y
Group By UNIX Time	Y
Histogram Floating Point Values	Y
Histograms	Y
Kerberos Authentication	Y			To enable Kerberos authentication, see Connect to Spark SQL Sources on a Kerberized HDP Cluster.
Last Value	Y
Live Mode and Playback	Y
Multivalued Fields	N/A
Nested Fields	N/A
Partitions	Y
Pushdown Joins for Fusion Data Sources	Y
Schemas	Y
Text Search	N/A
TLS	N
User Delegation	N
Wildcard Filters	Y
Wildcard Filters, Case-Insensitive Mode	Y
Wildcard Filters, Case-Sensitive Mode	Y

Connect to Spark SQL

When establishing a connection to Spark SQL, you need to provide the following information when setting up the partition settings.

Configure the partition settings. For the partitioned fields you can select one of the following options:

• No
• Date - this option is available for the Time field type. If you select this option, the list of the partitioned columns will be displayed in the Configure column.

Numeric and time-based fields can be edited using the Fields tab:

• Numeric type Number - ability to select a default aggregation function
• Time fields - ability to define the default time pattern and granularity; if the time field provides granularities of hour, minute and second, then a time zone label may be applied.

When you create a data source, the specific number of distinct values for the attribute fields are saved in Self-Service Analytics depending on the data sample from your data set. You can filter the data on your visual by these values. While editing a data source, if you want to use all distinct values in the filter (that is from whole data source), select Refresh in the Statistics column.

Connect to Spark SQL Sources on a Kerberized HDP Cluster

A secure Hortonworks Data Platform (HDP) cluster uses Kerberos authentication to validate and confirm access requests. You can set up Self-Service Analytics to connect to the secure HDP cluster using the following instructions.

Prepare the Spark SQL Cluster

• To enable Kerberos for HDP distribution using a Spark SQL source, refer to Hortonworks’ documentation Enabling Kerberos Authentication Using Ambari.
• Kerberos authentication requires precise time correspondence on all instances to work properly. You need to enable the Network Time Protocol service in your network. See Use the Network Time Protocol to Synchronize Time.
• Set up a Thrift JDBC/ODBC server in your environment. See Spark documentation.

Configure Self-Service Analytics Microservices

Obtain Kerberos Credentials

Each microservice must have its own unique identifier called a principal. Perform the following steps:

1. Install the Kerberos client on the CentOS or Ubuntu machine where the Self-Service Analytics server resides.

2. Generate the Kerberos principal and corresponding keytab for the Self-Service Analytics microservice. Before you proceed, make sure that:

   • The Self-Service Analytics microservice is running on a node with proper Kerberos configuration: /etc/krb5.conf or similar location for your Linux distribution.
   • The Kerberos realm on your environment is the same as the realm specified in the kdc.conf file from the Spark SQL server.

3. Check the Kerberos configuration (that is, krb5.conf) and validity of the principal and keytab pair using MIT Kerberos client:

   kinit -V -k -t <composer_principal>.keytab <[e-mail]>
   

4. Make the keytab accessible for Self-Service Analytics’s Spark SQL connector:

   sudo mkdir /etc/zoomdata sudo mv <composer_principal>.keytab /etc/zoomdata sudo chown zoomdata:zoomdata /etc/zoomdata/<composer_principal>.keytab sudo chmod 600 /etc/zoomdata/<composer_principal>.keytab
   

Configure a Spark SQL Connector

1. Create or update the file named /etc/zoomdata/edc-sparksql.properties. If this file already exists, verify that the information below exists in the file:

   kerberos.krb5.conf.location=/etc/krb5.conf kerberos.service.account.authentication=true kerberos.service.account.principal=<[e-mail]> kerberos.service.account.keytab.location=/etc/zoomdata/<composer_principal>.keytab
   

2. Restart the Spark SQL connector:

   sudo systemctl restart zoomdata-edc-sparksql
   

Connect to the Kerberized Spark SQL Source

You are now ready to create the Spark SQL source:

1. Open a new browser window and log into Self-Service Analytics.

2. Select Sources.

3. Select Spark SQL.

4. Specify the name of your source and add a description (if desired). Then select Next.

5. On the Connection page, define the connection source. You can use an existing connection, if available, or create a new one. To create a new connection, select the Input New Credentials option button and specify the connection name and JDBC URL. Make sure that you enter the JDBC URL in the correct format:

   jdbc:hive2://<spark-sql-host>:10000/;principal=<[e-mail]>
   

   Replace the placeholders as follows:

   • <spark_sql_host>: Specify the IP address or host name of the Spark SQL node to which you are connecting.

   • <[e-mail]>: Enter the principal of the Spark SQL node you are connecting to. To get the list of all Spark SQL principals, navigate to Ambari > Admin > Kerberos > Advanced > Spark SQL.

     The principal spec contained in the JDBC URL refers to the principal of the Spark SQL node. [e-mail] principal has nothing to do with the [e-mail] principal specified for the Self-Service Analytics connector.

6. Select Validate and, after your connection is valid, select Next.

You can continue configuring the data source as described in Create and Manage Data Sources.

After you have completed the configuration, Self-Service Analytics will begin accessing Spark SQL using [e-mail] authenticated by its keytab in /etc/zoomdata/<composer_principal>.keytab.