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
2 answers

How to use subquery in Hive

I can run below subquery command in MySQL, but it is not working in Hive. Is the Hive format different for subquery from MySQL? Question: Write query in hive that shows all orders belonging to a certain day. This day is when the most orders were…
user2774120
  • 139
  • 3
  • 16
1
vote
2 answers

How does Hive order numbers?

I noticed a weird Hive behaviour. It seems to not order columns right. this what I did: select TAUX_REMU_RESEAU from t where id='000000010302'; // returns 7423.00 select TAUX_REMU_RESEAU from t order by TAUX_REMU_RESEAU desc; // and the first…
Haha
  • 973
  • 16
  • 43
1
vote
0 answers

How to pass multiple value for a single variable in hive query?

I have a 'Proc SQL' query: %macro customers_metrics (me_dt, month_end); PROC SQL; CREATE TABLE raw.&month_end. AS select * from table where eff_date <= &me_dt. AND end_date >= &me_dt.; quit; %mend; %customers_metrics('2018-01-31',…
Aket Lohia
  • 31
  • 2
1
vote
1 answer

Subqueries in HIVE

I have this nested subquery in HIVE. When I do 1 subquery my results return fine. However when I attempt to add the second subquery I receive the following error. Prepare error: org.apache.hive.service.cli.HiveSQLException: Error while compiling…
Mark
  • 327
  • 1
  • 7
  • 14
1
vote
1 answer

Group by hourly interval

I'm new to SQL and I have problems when trying to make an hourly report on a database that supports HiveSQL. Here's my dataset |NAME| CHECKIN_HOUR |CHECKOUT_HOUR| |----|--------------|-------------| | A | 00 | 00 | | B | …
Helen
  • 53
  • 1
  • 7
1
vote
1 answer

Group by where category having max value

Here is my ttable +---------------+------------------------------+---------------+--+ | trading_year | sector | total_volume | …
Saif Kazi
  • 67
  • 3
  • 9
1
vote
2 answers

How to I convert Timestamp in YYY-MM-DD HH:mm:ss to YYY-MM-DD HH:mm:ss.SSS in Hive while doing a select query?

I am comparing timestamp columns between 2 different database engines and I need to retrieve the time stamp column stored in YYY-MM-DD HH:mm:ss format to YYY-MM-DD HH:mm:ss.SSS, with SSS being 000 when no entry is there. Can I do the above using…
SMT
  • 96
  • 1
  • 4
1
vote
3 answers

How to extract a part of a decimal in Hive

I have two columns called quantity and price. Quantity is divided by price. If the result contains decimal, I want the number before the decimal. Or else, the number as it is.
Mona
  • 273
  • 1
  • 2
  • 13
1
vote
2 answers

Hive query to map 3 array columns position wise

i/p: c1 c2 c3 [[1,2,3],[4],[5,6]] ['v1','v2','v3'] [['sam'], ['tam'], ['bam']] o/p: c1 c2 c3 [1,2,3] 'v1' …
pirate_shady
  • 73
  • 6
1
vote
1 answer

Update the column format from string to date in Hive

I've an external table in Hive Current: col1 - string col2 - string col3 - string col4 - float col5 - int I want to change the date type of col3 to date Expected: col1 - string col2 - string col3 - date col4 - float col5 - int I tried regular sql…
NewLearner
  • 59
  • 1
  • 8
1
vote
0 answers

Hive spatial functions

When I do Select ST_Point(25,45) in PostgresQL I get the following : "010100000000000000000039400000000000804640" , code I am trying to run the same spatial functions in hive and my code is as below: **add jar…
Nag
  • 11
  • 2
1
vote
1 answer

Hive table deduplication across multiple partitions

I am trying to de duplicate a table that may have duplicates across partitions. For example id device_id os country unix_time app_id dt 2 2 3a UK 7 5 2019-12-22 1 2 3a USA 4 5 …
George Annan
  • 15
  • 4
1
vote
2 answers

Best way to implement SCD1 in hive

I have a master table (~100mm records) which needs to be updated/inserted with daily delta that gets processed every day. Typical daily volume for delta would be few hundred thousand records. This can be implemented using full join or windowing…
Abdul Quddus
  • 11
  • 2
1
vote
1 answer

SQL parse string separated by | into keys and values

i need help on parsing strings which are separated by pipe. the first number is the key, and the subsequent string after pipe are values for that key in hundreds |01|00109394|05|84|08|34353637 this should be broken into keys which are the length=2…
Samy
  • 47
  • 7
1
vote
2 answers

Javascript Date conversion in Hive

I have a date column as a string data type in MMMM Do YYYY, HH:mm:ss.SSS (December 16th 2019, 21:30:22.000) format. I'm trying to convert this into a timestamp data type in hive but couldn't able to achieve it because this format is not available…
Sathyaraj
  • 189
  • 6
  • 21
1 2 3
…
99 100