Hive insert query like SQL
Some of the answers here are out of date as of Hive 0.14
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingvaluesintotablesfromSQL
It is now possible to insert using syntax such as:
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2));
INSERT INTO TABLE students
VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
Insert data into hive table
You can insert new data into table by two ways.
Load the data of a file into table using load command.
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename.
You can insert new data into table by using select query.
INSERT INTO table tablename1 select columnlist FROM secondtable;
Convert value while inserting into HIVE table
Based on your insert
, you just need to work on the role
part of your select
:
INSERT into TABLE bucketed_user PARTITION (salary)
select
id
, age
, gender
, if(role='consultant', 'BigData consultant', role) as role
, salary
FROM
stage_table
where
salary > 10000
;
Related Topics
MySQL Query to Select Everything Except
Best Way to Get the Next Id Number Without "Identity"
Custom Post Type Yearly/ Monthly Archive
Oracle: How to Get Percent of Total by a Query
How to Add a Variable Number of Hours to a Date in Postgresql
Table in Excel from SQL Server Stored Procedure with Parameter Field in Workbook
How to Select the Comparison of Two Columns as One Column in Oracle
How to List User Defined Types in a SQL Server Database
Postgresql Prefix Wildcard for Full Text
Concatenate/Merge Array Values During Grouping/Aggregation
How to Find the Average Value in a Column of Dates in SQL Server
What Are the Pros and Cons of Using Multi Column Primary Keys
Sqlite: Preventing Duplicate Rows
How to Manually Execute SQL Commands in Ruby on Rails Using Nuodb