Enabling hive Authorization in Qubole

Once the Hive authorization is enabled in qubole we need to manage the users and permission by Hive authentication,  following are the some of the commands which will be used for the same.

1. Listing the Current Roles

Set role admin;

show roles

2. Create the roles

CREATE ROLE <role_name>;
Creates a new role. Only the admin role has privilege for this.

Eg:

Set role admin;
Create role sysadmin;

3. Grant Role to users

GRANT ROLE <role_name> TO USER <user_name>
 

Eg:

Set role admin;

Grant Role sysadmin to user rahul ;

4. Revoke a role from user

REVOKE ROLE <role_name> FROM USER <user_name>;

Eg:

Set role admin;
REVOKE Role sysadmin from user rahul;

5. List  Roles attached to a user

SHOW ROLE GRANT USER <user_name>;

Eg.

Set role admin;

show role grant user `rahul`;

6. List Users under a role

SHOW PRINCIPALS <Role_name>

Eg

Set role admin;

SHOW PRINCIPALS sysadmin

7. Assign Role access to tables

Sample Permission

SELECT privilege: It provides read access to an object (table).
INSERT privilege: It provides ability for adding data to an object (table).
UPDATE privilege: It provides ability for running UPDATE queries on an object (table).
DELETE privilege: It provides ability for deleting data in an object (table).
ALL privilege: It provides all privileges. In other words, this privilege gets translated into all the above privileges.

GRANT <Permission> ON <table_name> TO ROLE <role_name>;

Eg:

Grant all on default.testtable to role sysadmin

8. View Role/user Permissions on tables

Check all users who have been granted with a specific role

SHOW GRANT USER <user_name> ON <table_name|All>;
SHOW GRANT ROLE <user_name> ON <table_name|All>;

Eg:

SHOW GRANT user analytics on all

Parsing Value from a Json Field in Qubole.

Description of how to extract a value from a JSON field in Hive using the get_json_object function. When the data in one of the fields in the Hive environment is in JSON format, and we need to extract a value out of the JSON, we can use the get_json_object function. For example, if we have a column named jdata containing the following JSON:

get_json_object(column_name, '$.keyvalue')

The column name is : jdata and json the Column is as followes.

{
    "Foo": "ABC",
    "Bar": "20090101100000",
    "Quux": {
        "QuuxId": 1234,
        "QuuxName": "Sam"
    }
}

if we have to extract ABC : get_json_object(jdata, '$.Foo') 

Qubole load CSV with spark

This is a code snippet using Spark on Qubole to load a CSV file into a DataFrame, register it as a temporary table, and create a permanent table from the data in the temporary table.

The first line of code reads the CSV file from an S3 location into a DataFrame. The options set for the format, delimiter, header, and inferSchema specify how the CSV file should be read and parsed.

val df = sqlContext.read.format("com.databricks.spark.csv")
                    .option("delimiter", "|")
                    .option("header", "true")
                    .option("inferSchema", "true")
                    .load("s3://*****.CSV")

The second line of code registers the DataFrame as a temporary table, which can be used for querying.

df.registerTempTable("temp-table")

The third line of code creates a permanent table in a specified database by executing an SQL query on the temporary table. The query selects all the columns and rows from the temporary table and creates a new table with the same data in the specified database.

sqlContext.sql("""
create table database.table as
select * from temp-table
""")

Qubole : Load Multiple tables to Qubole Hive table from a Data Store

API call to Load Multiple tables from a Qubole Data Store to Hive table. 

[rahul@local qubole]$ cat /databasescript 

#!/bin/bash

#Qubole API Key

AUTH="***********"

#Database Name

DB_NAME="***********"

#Host Name

DB_HOST="***********"

#User Name

DB_USER="***********

#Password 

DB_PASS='***********'

echo $DB_PASS

## request table import from tap;

function tableImport() {

request_body=$(cat <<EOF

{

   "command_type":"DbImportCommand",

   "mode":"1",

   "hive_serde":"orc",

   "hive_table":"<HIVE TABLE NAME>.$1",

   "dbtap_id":"$2",

   "db_table":"$1",

   "db_parallelism":"1",

   "use_customer_cluster":"1",

   "customer_cluster_label":"Qubole_Data_Import",

   "tags":[" Data"]

}

EOF

)

echo $request_body

   curl -X POST \

-H "X-AUTH-TOKEN: $AUTH" \

-H "Content-Type:application/json" \

-d "$request_body" https://api.qubole.com/api/v1.2/commands/

}

##register database with tap

request_body=$(cat <<EOF

{

  "db_name":"$DB_NAME",

  "db_host":"$DB_HOST",

  "db_user":"$DB_USER",

  "db_passwd":"$DB_PASS",

  "db_type":"sqlserver",

  "db_location":"on-premise",

  "gateway_ip": "***********",

  "gateway_port": "***********",

  "gateway_username": "***********",

  "gateway_private_key": "***********"}

EOF

)

echo $KEY

ID=$(curl -s -X POST \

-H "X-AUTH-TOKEN: $AUTH" \

-H "Content-Type:application/json" \

-d "$request_body" https://api.qubole.com/api/v1.2/db_taps/ | jq .id)

#get the tables and call import

curl -s -H "X-AUTH-TOKEN: $AUTH" \

     -H "Content-Type:application/json" \

     https://api.qubole.com/api/v1.2/db_taps/$ID/tables | jq -r .[] | while read x; do  tableImport $x $ID; done

# can't delete the tap at the end unless we continuously poll for no active jobs;

STATUS="null"

while [ "$STATUS" = "null" ]

do

STATUS=$(curl  -s -X DELETE \

 -H "X-AUTH-TOKEN: $AUTH" \

 -H "Content-Type:application/json" \

 https://api.qubole.com/api/v1.2/db_taps/$ID | jq .status)

echo -n "."

sleep 5

done