Row level transformation in Hive


#1

kindly assist please with command to eliminate the error in this returm

FAILED: ParseException line 1:47 Failed to recognize predicate ‘order’. Failed rule: ‘identifier’ in table or column identifier
hive (default)> select concat(substr(order_date, 1, 4), substr(order_date, 6, 2))
> from orders limit 10;
FAILED: SemanticException [Error 10004]: Line 1:21 Invalid table alias or column reference ‘order_date’: (possible column names are: id, date, total, status)
hive (default)>


#2

@Chris

You need to use your own database and create table orders and try to run the queries. Follow below steps to run the query.

Step 1
Create your own database with any name you want in the hive by using below command

create database **dbname**;

Step 2
Change to your database from the hive(default) use below command for it.

use **dbname**;

Step 3
After switching to the database create table orders using below command

create table orders (
 order_id int,
 order_date string,
 order_customer_id int,
 order_status string
) row format delimited fields terminated by ','
stored as textfile;

Once the table is created use the load command to save the data into the orders table.

load data local inpath '/data/retail_db/orders' into table orders;

Step 4
Run the query using below command

select concat(substr(order_date, 1, 4), substr(order_date, 6, 2))
from orders limit 10;

Let me know if you are having any further issues regarding this.