Stack Overflow
Stack Overflow

Questions tagged [hiveql]

Variant of SQL used in the Apache Hive data warehouse infrastructure. Use this tag for questions related to the Hive Query Language including HiveQL syntax and HiveQL-specific functions.

HiveQL is the dialect of used in Apache Hive. HiveQL is similar to other dialects of SQL, but has some limitations (e.g. absence of nested subqueries and top-level disjuncts) and additional features (e.g. MapJoin), compared to most other dialects. Apache Hive is a infrastructure package built on the distributed-computing framework, written in Java.

Please read How to write good SQL question section in SQL tag wiki and follow the same rules.

References

4862 questions
1
vote
1 answer

HiveQL - first_value of multiple columns over window

I am looking to retrieve the first row and last row over a window in HiveQL. I know there are a couple ways to do this: Use FIRST_VALUE and LAST_VALUE on the columns I am interested in. SELECT customer, FIRST_VALUE(product) over…
penguin
  • 439
  • 1
  • 5
  • 13
1
vote
1 answer

Hive: How to output total row count as a variable

I have a dataset that I'm de-duping with the following code: select session_id, sol_id, id, session_context_code, date from ( select *, ROW_NUMBER() OVER (PARTITION BY session_id, sol_id, date) as rn, substr(case_id,2,9) as id …
lydias
  • 841
  • 1
  • 14
  • 32
1
vote
1 answer

Hive partition column

We have avro partitioned table in hive. When we query table, partition column is displaying at the end. Is there any way to display partition column at first? Eg: select * from tablea Output: Col1 col2 partition_column Expected…
user11069271
  • 109
  • 2
  • 6
1
vote
1 answer

running hive command through shell getting error

hive -e "select key from hbcv_table a where a.col2 like \'%hello friend%\'" | sed 's/[\t]/,/g' >a.dat getting errror like - unexpected EOF while looking for matching `"' Can any one please help how to fix it.
Rakesh Bagaria
  • 27
  • 5
1
vote
1 answer

Hive: Query to get max count per word per date

Here's the data I have: date | word | count 01/01/2020 #abc 1 01/01/2020 #xyz 2 02/05/2020 #ghi 2 02/05/2020 #def 1 02/04/2020 #pqr 4 02/04/2020 …
suhabari
  • 135
  • 6
1
vote
1 answer

How to condense multiple where statements in Hiveql across range of columns: where icd_proc_cd_1='43644' or ... or icd_proc_cd_28='43644'

Is there a more elegant way of condensing a where statement over a range of variables with indexed names? For example instead of: create table table_cpt_43644 as select * from master_table where icd_proc_cd_1 = '43644' or icd_proc_cd_2 =…
RobertF
  • 824
  • 2
  • 14
  • 40
1
vote
1 answer

Hive server not coming up

Hive server is not coming up with following error. I cannot re-initialize hive-metastore because i do not want to lose data which is already there in cluster. Cluster settings are fine because we have been using the same cluster for more than a year…
sinshiva
  • 407
  • 6
  • 16
1
vote
2 answers

Constructing a date with year and month

I want to construct a date from just a year and month. I'm currently running a query like this: SELECT yyyy_mm_dd AS t.year, t.month, t.id, t.name, ..., ... ... FROM( SELECT p.last_yyyy_mm_dd, …
stackq
  • 491
  • 2
  • 15
1
vote
1 answer

HIVE-SQL_SERVER: HadoopExecutionException: Not enough columns in this line

I have a hive table with the following structure and data: Table structure: CREATE EXTERNAL TABLE IF NOT EXISTS db_crprcdtl.shcar_dtls ID string, CSK string, BRND string, MKTCP string, AMTCMP string, AMTSP string, RLBRND…
rocking
  • 109
  • 2
  • 7
1
vote
1 answer

Group by on single column and select multiple columns in hive

I have data in a Hive table as given below. Table 1: ID CODE VALUE 1 XXXX 100 2 AAAA 200 1 YYYY 300 3 DDDD 300 4 BBBB 200 2 CCCC 300 3 HHHH 200 I have to select ID, VALUE from Table 1 and load into Table 2.Since Table 1 has…
Surbhi Jain
  • 13
  • 3
1
vote
1 answer

How to convert values in the string to hex in Hive

I want to convert a string into hex, for example: there is a string as '[1,2,3,4,5]', I want to convert this to hex and the output should be in string as follow: '[31,32,33,35,35]' using hiveQL
Dhara Mandal
  • 11
  • 3
1
vote
2 answers

Selecting max date of each month

I have a table with a lot of cumulative columns, these columns reset to 0 at the end of each month. If I sum this data, I'll end up double counting. Instead, With Hive, I'm trying to select the max date of each month. I've tried this: SELECT …
stackq
  • 491
  • 2
  • 15
1
vote
1 answer

HIVE Obtain First Weekday of Current Month

Morning, Title says it all. I cannot for the life of me figure out how to obtain the first weekday of the current month (or previous month etc.) in HQL. So if today's date were to be evaluated, it should return 2/3/2020 as the date, since the 3rd…
Mark
  • 327
  • 1
  • 7
  • 14
1
vote
1 answer

SQL Regex throwing state=42000,code=40000

case regexp_extract(network_information,'^([\\w|-]+)[.|;].*',1) then ......... For this part of code I am getting ParseException line 22:2 cannot recognize input near '*' ',' 'case' in expression specification (state=42000,code=40000) Can anyone…
connecttopawan
  • 208
  • 3
  • 14
1
vote
2 answers

Hive - find 2 characters anywhere in the string/row - RLIKE

How do I get the data for ONLY “_WA” data assigned to "USA_RBB_WA_BU"? However the column I look at has rows that contain _WA and _SA (USA_CA_SAWANT) I used, select.... 'USA_RBB_WA_BU' AS State , …
YJG
  • 123
  • 2
  • 12
1 2 3
…
99 100