Sorry for my bad english. I want to get HIDESC from M6HI based on HI.HIRECO = RE.RECODE and RE.RECODE is loop from M5RE. Anyone know how to fix it? Answer A Common Table Expression may help you break this down a bit. HiveQL supports CTE’s. Documentation is here: https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression
Tag: hiveql
HIVE-SQL_SERVER: HadoopExecutionException: Not enough columns in this line
I have a hive table with the following structure and data: Table structure: Trying to push this data into the SQL Server. But while doing so, getting the following error message: What I tried: There’s an online article where the author has documented similar kind of issues. I tried to implement one of them Looked in Excel and found two
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…. …
Convert from lateral view to case statements in hive
I need to place below code in case statement: I have tried: But getting error. Answer For the provided SQL on table tab1, the actual logic is like: Explode field secondary.tertiary, alias it as lv, which results in a temporary result set (table) tab2; A join-like operation to concatenate tab2’s fields back to rows in tab1, resulting in another intermediate
Add missing monthly rows
I would like to list the missing date between two dates in a request for example My data : YEAR_MONTH | AMOUNT 202001 | 500 202001 | 600 201912 | 100 201910 | 200 201910 | …
Dynamically frame Filter condition based on conditions
We have separate table maintained for condition / filters. Based on the conditions, filters to be applied at base table. here’s the sample input conditional data for reference purpose Based on this input conditions , filter to be derived as follows. please help me in achieving the filter query Answer The below sparkSQL will help you to build the where
Why I am getting error while performing group by in hive?
I am executing below command in hive: Select child.data_volume_gprs_dl + child.data_volume_gprs_ul as data_usage, parent.file_name, parent.record_number from table1 as parent left …
Sql Query / Calculate rank for values based on date and id
For each date and id we have to give the rank to values in decreasing order. Below is the Input Table. Output should look like below: Answer You can use RANK(), partitioning on date and id and ordering by value descending: Output: Demo on SQLFiddle This query will run on all the DBMS you have tagged your question with.
In Hive, how to convert an array of string to an array of numeric numbers
I have a hive table that looks like the following: I want the result to be the following: I need to convert them into an array of float so that I can use them in ST_Constains(ST_MultiPolygon(), st_point()) to determine if a point is in an area. I am new to Hive, not sure if that is possible, any help would
Generate range hours and range numbres SQL/HQL
I have a problem with a table.I currently have this empty hours table and I need to fill it automatically with a query in Hiveql. The idea is to generate: In the first column “key” values between 000000 and 235959 In the second column “hours” values between 00:00:00 and 23:59:59. Now my table is empty: Future table that I need