convert normal column as partition column in hive

I have a table with 3 columns. now i need to modify one of the column as a partition column. Is there any possibility? If not, how can we add partition to existing table. I used the below...

How to get the URL for Hive Web Interface

Sorry, it may be a basic question. I tried to google it but couldn't find exact solution I am trying to find out URL for my Hive web interface. Through this I can check the tables present in it....

How to query struct array with Hive (get_json_object)?

I store the following JSON objects in a Hive table: { "main_id": "qwert", "features": [ { "scope": "scope1", "name": "foo", "value": "ab12345", "age": 50, ...

explode function in hive

I have the following sample data and I am trying to explode it in hive.. I used split but I know I am missing...

Exception after Setting property 'spark.sql.hive.metastore.jars' in 'spark-defaults.conf'

Given below is the version of Spark & Hive I have installed in my system Spark : spark-1.4.0-bin-hadoop2.6 Hive : apache-hive-1.0.0-bin I have configured the Hive installation to use MySQL as...

How to create a table with dates in sequence between range in Hive?

I'm trying to Create a table with column date, And I want to insert date in sequence between Range. Here's what I have tried: SET StartDate = '2009-01-01'; SET EndDate = '2016-06-31'; CREATE...

How to execute Hive hql files from java?

I understand I can do it by parsing the hql file, extracting individual queries and executing each query separately, but I want to know whether there's a better and more graceful way to run a...

error when inserting data to a partitioned table in Hive

I am seeing an error when I try to insert data into a partitioned table in hive, here is the details: The table: CREATE TABLE partitionedemp(emp_no int, birth_date string, first_name string,...

Sort field in hive

I have table about 20-25 million records, I have to put in another table based on some condition and also sorted. Example Create table X AS select * from Y where item <> 'ABC' Order By id; I...

Using RANK in HiveQL, dynamic limits

Trying to perform a dynamic limit in hive sql via the rank function. PROBLEM: I want to use the limit from table A against table B to create the output. Example below. TABLE A: ID | Limit ...

How do we get the 1000 tables description using hive?

I have 1000 tables, need to check the describe <table name>; for one by one. Instead of running one by one, can you please give me one command to fetch "N" number of tables in a single shot.

how to obtain a particular value from a map(string,string) field?

I have a table with the following two columns The format of event_detail column is map(string,string) I want to obtain the visitor numbers who have value(B) in the event_detail

Hive query records processed count

I want to know how many records processed or % of records proccessed by a query to fetch result in hive. I tried describe formatted for query, but unable to do. describe formatted (select * from sample)

How to convert this MYSQL SQL to HIVE SQL?

The table ProductOrder columns include: id shopid starttime endtime 1 123 2018-04-27 2018-04-28 2 234 2018-04-23 2018-04-30 3 189 2018-05-01 2018-05-30 4 321 ...

How to insert backdated data in Hive Table?

I'm new to this technology. We receive the file which may contain backdated records. From which we have to load the data into the hive table which will be insert only(partitioned on trans_Date). I...

Converting only time to unixtimestamp in Hive

I have a column eventtime that only stores the time of day as string. Eg: 0445AM - means 04:45 AM. I am using the below query to convert to UNIX timestamp. select...

Hive How to create a table when using insert overwrite directory?

I am wondering if it's possible to create(insert overwrite) a table when you are doing insert overwrite directory? The table should have the same data as stored in the directory. Right now I have...

Calculate time difference between two columns of string type in hive without changing the data type string

I am trying to calculate the time difference between two columns of a row which are of string data type. If the time difference between them is less than 2 hours then select the first column of...

“Hive” max column value from multiple columns

Hi: I have a situation where I need to find the max value on 3 calculated fields and store it in another field, is it possible to do it in one SQL query? Below is the example SELECT Income1 , ...

Join tables with almost same schema in hive

I have table A with schema name string address string timezone string ...

SQL Error 10249 Hive with multiple subqueries

I am trying to run multiple sub queries into a where clause, and I get the error below. Does mean that Hive doesn't support it? If not, is there a different way to write the query below? If i...

How to Identify total number of jobs required to execute hive query

Is there a way to identify, total number of jobs required to execute a query. For Example in the below 2 queries, number of joins and subquery are same but one query would require 2 jobs where as...

how to use regexp_extract in hive

I am trying to extract a portion of the below string using regexp_extract but am not having any success: CUST_NEW_ACCOUNTS_LINES_2019-03-03.dat.gz I want to just get the date portion. On the...

How to explode quantiles in hive

I am trying to get quantiles of a field and I want to explode them so that each value is a separate row rather than all of them forming a single array. First, I calculate 20 quantiles as...

Hive: join tables with array without LATERAL VIEW explode

I have searched the way to join tables with array coumn on hive, but all answers suggest to use LATERAL view EXPLODE. I want to get the data like below ; +---------+----------+----------+ | name ...

Getting last day of previous quarter

When I run SELECT MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE())-1 QUARTER - INTERVAL 1 DAY here, it works as intended. However, in Hive I get an error that I'm missing a closing...

Percentile calculation in HIVE

How can I calculate 25 percentile in Hive using sql. Let's say there is category, sub category and sales column. So how can I calculate the 25 percentile of sales? I tried to use the...

Hive split string to get all the items except first one?

I have a column data "testdata" like this "abc,def,ghi,jkl" and I want to retrieve the output as "def,ghi,jkl" I am able to retrieve the first data like this SELECT split(testdata,'[\,]')[0] FROM...

Trying to get multiple rows with the values in the array in hive by lateral view and explode

I have a table with two columns and i am trying get multiple rows with the values in the array.My table is like this. date users 2019-01-01 ["U00001","U00002","U00002"] I...

Hive : How to flatten an array?

I have this table CREATE TABLE `dum`( `val` map<string,array<string>>) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT ...

Relevant tags