hive load data

USE testdb;

CREATE TABLE IF NOT EXISTS testdb.t1 (

  firstnameid    string,

  firstname      string,

  sex            string

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INPATH 'file:///root/work/t1.csv' OVERWRITE INTO TABLE testdb.t1;

CREATE TABLE IF NOT EXISTS testdb.t1 (

  firstnameid    string,

  firstname      string,

  sex            string

)

CLUSTERED BY (firstnameid) INTO 1 BUCKETS

STORED AS orc

TBLPROPERTIES('transactional' = 'true','orc.compress'='ZLIB','orc.create.index'='true');

INSERT INTO TABLE testdb.t2

SELECT

  CAST(firstnameid as INT), SUBSTRING(firstname,2,LENGTH(firstname)-2),

  SUBSTRING(sex,2,LENGTH(sex)-2)

FROM testdb.t1;

SELECT

  CAST(id as INT), SUBSTRING(datetimes,2,LENGTH(datetimes)-2),

  SUBSTRING(monthname,2,LENGTH(monthname)-2), CAST(yearnumber as INT),

  CAST(monthnumber as INT),  CAST(daynumber as INT), CAST(hournumber as INT),

  CAST(minutenumber as INT), SUBSTRING(ampm,2,LENGTH(ampm)-2)

FROM testdb.t3;

CREATE TABLE IF NOT EXISTS processdb.locationgeospace1satellite (

  id            INT,

  locationid    INT,

  postcode      STRING,

  timestmp      BIGINT

)

CLUSTERED BY (id) INTO 1 BUCKETS

STORED AS orc

TBLPROPERTIES('transactional' = 'true','orc.compress'='ZLIB','orc.create.index'='true');

TRUNCATE TABLE processdb.locationgeospace1satellite;

INSERT INTO TABLE processdb.locationgeospace1satellite

SELECT

  ROW_NUMBER() OVER (ORDER BY locationid),

  locationid,

  postcode,

  unix_timestamp()

FROM

  processdb.locationgeospacesatellite0001

ORDER BY locationid;