Impala

1. Creating a partitioned table in  parquet format directly from an unpartitioned table in different format.

create external table dbname.partitioned_table
PARTITIONED BY ( col5,col6 )
STORED AS PARQUET
LOCATION 'hdfs://nameservice/path/partitioned_table'
AS select
  col1 ,
  col2 ,
  col3,
  col4
  col5,
  col6
from  dbname.unpartitioned_table

2.   Error while accessing HIVE TEXTFILE format table in Impala.

Create a  TEXTFILE format table in HIVE. 

CREATE EXTERNAL TABLE test_impala_do (   category STRING,   segment STRING,   level_1 STRING,   group_code INT,   d_xuid_count INT,   segment_code INT,   datetime STRING COMMENT 'DATETIME',   source_file STRING COMMENT 'SOURCE_FILE' ) PARTITIONED BY (   akey STRING COMMENT 'AKEY AS DEFINED' ) WITH SERDEPROPERTIES ('serialization.format'='1') STORED AS TEXTFILE LOCATION 'hdfs://nameservice1/..../test_impala_do'

Populated using spark SQL job.

INSERT OVERWRITE INTO test_impala_do.....

if before populating the data I execute following.

sqlContext.setConf("hive.exec.compress.output","true")

then when I access the table in Impala , it gives exception ...could not load table metadata complaining that files should be compressed with some predefined extensions

If I populate without this conf , then it works fine.




Comments

Post a Comment

Popular posts from this blog

SQL

Joins - Not always very obvious ( Following example uses Hive ). create table x_temp ( name string, p_date int  ) insert into x_temp(name,p_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table y_temp ( name string, s_date int  ) insert into y_temp(name,s_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table z_temp ( name string, p_date int, s_date int ) insert into z_temp(name,p_date,s_date) values ('z1','20181025','20181025') select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date and  y.s_date != z.s_date) generates 4 records... x.name,x.p_date,y.name,y.s_date x2, 20181026, x2, 20181026 x3, 20181027, x2, 20181026 x2, 20181026, x3, 20181027 x3, 20181027, x3, 20181027 select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date or  y.s_date != z.s_date) generates 8 records x.name,x.p_date,y....
Read more

Analytics

Types of Analytics ( Notes from AWS Course )  Descriptive - What happened ?  hindsight  Diagnostic - Why did it  happen?  hindsight & insight  Predictive  - What will happen ? insight & foresight  Prescriptive - What should I do ? foresight  Cognitive & AI  - What are recommended actions ? foresight & hypothesis input  Popular BI Tools:  https://www.cio.com/article/3284415/10-bi-tools-for-data-visualization.html
Read more

DBeaver

Recently I used this tool for connecting to Hive via Knox gateway.  Good thing about this tool is that it automatically downloads the driver and configures it. That  is a pain in tools like Squirrel.  It also makes a JDBC connection to Hive.  Below is how the HOST and JDBC URL settings look like : HOST : <KNOX_HOST>:8443/;ssl=true;transportMode=http;httpPath=gateway/<topology>/hive JDBC URL :  jdbc:hive2://<KNOX_HOST>:8443/;ssl=true;transportMode=http;httpPath=gateway/<topology>/hive What I noticed on my setup was that if you keep the default port which 10000 and you set it to 8443 , it does not work. So you have to keep it blank.  That may be specific to my setup. Also you might notice that ssl=true but there are  no  sslTrustStore= < Path to the “.jks” file in the NiFi node > ;trustStorePassword= < TrustStore Password>  parameters. The reason is if you import the gateway cert  into the trus...
Read more