Converting string to map data type in Hive

Converting string to map data type in Hive

If  you have a string with key value pairs and want to convert to a map data type with key value pairs then use the str_to_map function.

str_to_map(text, delimiter1, delimiter2) - Creates a map by parsing text Split text into key-value pairs using two delimiters. The first delimiter seperates pairs, and the second delimiter sperates key and value. If only one parameter is given, default delimiters are used: ',' as delimiter1 and '=' as delimiter2.

Example:

with t1 as (
select "k1:v1|k2:v2|k3:v3" as c1
 )


select str_to_map(c1 ,"\\|",":") as m1, c1 from t1

Difference between posexplode and explode in Hive

Difference between posexplode and explode in Hive

In the below example, the numbers column has a list of values.

with t1 as (

select 1 as id, 'deepak' as name, '12345, 56789, 7892334, 6263636, 7181818, 1761761717' as numbers )

select * from t1

Explode is used to create multiple rows from a list value in a column. In the below example, you have broken the list in numbers fields into multiple rows.

with t1 as (

select 1 as id, 'deepak' as name, '12345, 56789, 7892334, 6263636, 7181818, 1761761717' as numbers )

select * from t1

LATERAL VIEW explode(split(numbers, ',')) test

PosExplode is used to create multiple rows from a list value in a column. In the below example, you have broken the list in numbers fields into multiple rows along with the column called pos that contains the  position of the value in the list.

with t1 as (

select 1 as id, 'deepak' as name, '12345, 56789, 7892334, 6263636, 7181818, 1761761717' as numbers )

select * from t1

LATERAL VIEW posexplode(split(numbers, ',')) test

Submitting pyspark jobs on Yarn and accessing hive tables from spark

Submitting pyspark jobs on Yarn and accessing hive tables from spark 

I was getting the below error while trying to access hive table through pyspark while submitting the job on spark.

yspark.sql.utils.AnalysisException: u'Table not found: `prady_retail_db_orc`.`orders`;'

I had to then start accessing the table through the hive context to fix the issue. 

The Command to submit the job is:

spark-submit --master yarn --conf "spark.ui.port=10111"  test2.py

The above command submit the test2.py program on yarn on port 10111.

The contents on test2.py is below. It is accessing a hive table called orders and writing the contents of the table in parquert format to hdfs location.

----------------test2.py contents----------------

from pyspark import SparkContext, SQLContext

from pyspark.sql import HiveContext

sc=SparkContext()

sqlContext = HiveContext(sc)

dailyrevDF=sqlContext.sql("select * from prady_retail_db_orc.orders")

dailyrevDF.write.mode("overwrite").format("parquet").option("compression", "none").mod

e("overwrite").save("/user/prady/data/test7")

How to make hive queries run faster?

The options available are

1) Use ORC file format which provides better performance than Text.

2) Use Tez execution engine

3) Use Cost based optimization

4) Use Vectorization execution

5) Right sensible queries that avoids joins

Reading from Amazon S3 using NiFi

Reading from Amazon S3 using NiFi

1) First create a bucket on Amazon S3 and create public and private keys from IAM in AWS

2) Proper permission should be provided so that users with the public and private keys can access the bucket

3) Use some S3 client tool to test that the files are accessible.

4) Create the dataflow on Nifi using ListS3 , FetchS3Object and PutS3 object as shown in the diagrams below.

5) Setting of ListS3 is listed below. S3-kannadiga is bucket name in US-East region. The access key and secret key is entered in this processor. 

6) Setting of Fetch S3 is given below. $(s3.bucket) is the setting to read from List S3 processor.

 7) The setting of PutFile is given below.

Sample parameter file and using parameter file in Informatica

Sample parameter file and using parameter file in Informatica

Below is a sample of a simple parameter file. All the global variables used by all the sessions are defined under [Global] and the session specific variable overrides are defined under session names.

-----------------------------------------------------

[Global]
$DBConnection_VSTP=VSTPDW_HIST_US

[s_MYSQL_EXT_Load5]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC1.EXT.G2016.txt

[s_SRC2_EXT_Load]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC2.EXT.G2016.txt

[s_SRC3_EXT_Load]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC2.EXT.G2016.txt

[s_SRC4_EXT_Load]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC14.EXT.G2016.txt
-------------------------------------------------------------------------

The parameter file can be set at session level or at workflow level. The worklevel parameter applies to all the session unless it is over riden by another parameter file at session level.

Find all columns with only nulls in SQL Server table

Find all column fields with only nulls in SQL Server table

The piece of code below will give list of all columns that have only nulls in a give table. Replace YourTableName with your tablename in the code below. This is variation of code that I found here (
http://stackoverflow.com/questions/63291/sql-select-columns-with-null-values-only) 

CREATE TABLE #NullColumns (ColumnName Varchar(100))
DECLARE @TableName Varchar(100)
SET @TableName='YourTableName'
DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID(@TableName)
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('INSERT INTO #NullColumns (ColumnName) SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM ' + @TableName + ' WHERE ' + @name + ' IS NOT NULL)')
    FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs
SELECT * FROM #NullColumns
DROP TABLE #NullColumns