Connecting to Query Server

About Connecting to the Query Server Database

To query data in the Hadoop cluster, users can connect to the Query Server if it is installed. During the Big Data SQL installation, the Big Data SQL installer creates and installs the BDSQLUSR Query Server database on the edge node that you specified. In addition, the installer also installs everything else that you need on that edge node to enable you to query data in the Hadoop cluster.

Query Server users connect to a pluggable database called BDSQLUSR. There are multiple ways to connect to the database, depending on whether the cluster is secure or non-secure.

On non-secure clusters, users can connect using a database username/password. On secure clusters, users can connect to the database using both Kerberos principals and/or regular database users/passwords.

In both secure and non-secure clusters, database users must be created using the DBMS_BDSQS_ADMIN PL/SQL package. Furthermore, in unsecure clusters, the Query Server has a single pre-configured database user, called bdsql, whose password is set during the Query Server installation.

By default, the Query Server is configured to use TLS connections. In order to connect with TLS, you must first copy the client wallet from the Query Server to the client nodes. If TLS is disabled, the client wallet is not needed.

Connecting to Non-Secure Hadoop Clusters

Queries run on the cluster as one of two users depending on how Query Server authorization has been configured. When the Query Server is setup with multi-user authorization, queries will run on the Hadoop cluster as OS user bdsql. Without multi-user authorization configured, the single-user model is used and queries will run as the user oracle.

With the single-user model, all users connect to the BDSQLUSR Query Server database as user bdsql with a password that the administrator chooses during the Query Server installation.

Queries run on the cluster with the oracle user permissions. This means that the oracle user must be authorized to access the underlying Hadoop data – either by using Sentry privileges (in CDH clusters) and/or HDFS authorizations. For example, you can connect to the Query Server database using SQL*Plus as follows:

sqlplus BDSQL/<bdsql_password>@BDSQLUSR

Note

Substitute <bdsql_password> in the above command with the actual BDSQL password that the administrator specified during the Oracle Big Data SQL installation.

Changing the BDSQL User Password

When installing Cloud SQL Query Server on a nonsecure cluster, you can change the password of the BDSQL user with

ALTER
                    USER

as follows:

sqlplus / as sysdba
sql> alter user user identified by 'new_password' replace 'current_password';

You can also change the password for the current session only:

# su - oracle
sqlplus / as sysdba
sql> alter session set container=bdsqlusr;
sql> alter user bdsql identified by "new_password";

Note

Substitute new_password with the new password. The new password must conform to the required Oracle secure password guidelines. See Guidelines for Securing Passwords in Oracle Database Security Guide for information about choosing your new password.

Note that on a Kerberos-secured cluster, the user BDSQL is disabled.

Connect to Secure Hadoop Clusters with Kerberos Authentication

On secure clusters, you can connect using externally identified database users corresponding to Kerberos principals. Before connecting to the Query Server, you must authenticate with Kerberos using kinit.

You use this mode when there are multiple externally identified user accounts, corresponding to Kerberos principals. Connected users' identities (Kerberos principals) will be used for authorization on the Hadoop cluster when the impersonation_enabled parameter is set to true in the bds-config.json configuration file. If this parameter is set to false, then authorization on the Hadoop cluster will be performed as user oracle. Sentry is used on clusters using Cloudera Distribution including Hadoop. HDFS authorization is used for clusters using Oracle Distribution including Apache Hadoop.

Note

See The Multi-User Authorization Model in Oracle Big Data SQL User's Guide to learn how to use Hadoop Secure Impersonation to direct the oracle account to execute tasks on behalf of other designated users.

Hadoop queries executing on the cluster on behalf of a user will appear to the Hadoop nodes as the authenticated user and will have the corresponding permissions. The same is true for Hive queries and for statistics gathering operations. Before you can connect to Query Server, you must be authenticated with Kerberos using kinit. When you install or reconfigure Big Data Query Server on a secure cluster, Jaguar collects all principals from the Key Distribution Center (KDC) running on nodes where Hadoop DataNodes are also installed. For each principal, an externally identified user will be created on Big Data SQL Query Server. This install-time behavior is controlled by the syncPrincipals parameter in the bds-config.json configuration file. This operation can also be invoked by running the following command (notice that the spelling of the Jaguar operation is different):

jaguar sync_principals

You can also use the DBMS_BDSQS_ADMIN package which contains procedures to add and drop Query Server users. These Query Servers users are the same Kerberos principals that will be accessing your Hadoop cluster.

DBMS_BDSQS_ADMIN.ADD_KERBEROS_PRINCIPALS(principals_list varchar2, op_semantics varchar2 DEFAULT 'STOP_ON_FIRST_ERROR')

DBMS_BDSQS_ADMIN.DROP_KERBEROS_PRINCIPALS(principals_list varchar2, op_semantics varchar2 DEFAULT 'STOP_ON_FIRST_ERROR')

Note

Before you can run the procedures in the DBMS_BDSQS_ADMIN package, you must connect to Oracle Big Data SQL Query Server as user sys using OS authentication. For example, you can login to SQL*Plus as OS user oracle. See Oracle Big Data SQL Reference in Oracle Big Data SQL User's Guide. This information also applies to Cloud SQL.

Users in a multi-user mode can then connect to SQL*Plus without providing a password as follows:

[user_name@cluster_name ~]$ kinit user_name 
Password for user_name@cluster_name.mycompany.com: 
[user_name@cluster_name ~]$ sqlplus /@BDSQLUSR

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Oct 2 13:54:39 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 02 2018 13:54:20 -05:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL>

Note

In the above example, user_name and cluster_name reflects your actual username and cluster name.

Connect to Secure Hadoop Clusters with Password-Based Database Authentication

Besides using a Kerberos principal, you can also connect to the Query Server on a secure cluster as a database user with a password.

Obtain the client wallet for TLS-enabled connections.
Connect to the Query Server using the client username and password.
For example, in order to connect as user bdsql_user1 you can use:
```
> connect bdsql_user1/"<example-password>"@BDSQLUSR
```

Oracle Cloud Infrastructure Documentation

Connecting to Query Server