finally I insert with bucker like this: CREATE EXTERNAL TABLE IF NOT EXISTS journey_importe_v2(
FECHAOPRCNF date,
codnrbeenf string,
codnrbeenf2 string,
CODTXF string,
FREQ BIGINT,
IMPORTE DECIMAL(9, 2)
)
CLUSTERED BY (codnrbeenf) INTO 25 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
stored as ORC
LOCATION '/RSI/tables/logs/importe_v2'
TBLPROPERTIES ("immutable"="false","transactional"="true");
create table IF NOT EXISTS temp_journey_importe_v2 (importe STRING);
LOAD DATA INPATH '/RSI/staging/output/journey_importe/${date}' OVERWRITE INTO TABLE temp_journey_importe_v2;
set hive.enforce.bucketing = true;
INSERT INTO TABLE journey_importe_v2
SELECT
regexp_extract(importe, '^(?:([^,]*)\,?){1}', 1) FECHAOPRCNF,
regexp_extract(importe, '^(?:([^,]*)\,?){2}', 1) codnrbeenf,
regexp_extract(importe, '^(?:([^,]*)\,?){3}', 1) codnrbeenf2,
regexp_extract(importe, '^(?:([^,]*)\,?){4}', 1) CODTXF,
regexp_extract(importe, '^(?:([^,]*)\,?){5}', 1) FREQ,
regexp_extract(importe, '^(?:([^,]*)\,?){6}', 1) IMPORTE
from temp_journey_importe_v2;
there is a better way?? how many buckets recomend me use??
... View more
