
set hive.fetch.task.conversion=none;
set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.vectorized.execution.enabled=true;
set hive.vectorized.reuse.scratch.columns=true;
set hive.auto.convert.join=true;

CREATE EXTERNAL TABLE scratch_col_issue_txt(
  `id` int,
  `value` string,
  `date_string` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
LOCATION '../../data/files/scratch_col_issue_test_data';

CREATE TABLE scratch_col_issue
AS SELECT * FROM scratch_col_issue_txt;

DESCRIBE FORMATTED scratch_col_issue;

EXPLAIN VECTORIZATION DETAIL SELECT
  CASE WHEN scratch_col_issue.value in (
    'TermDeposit', 'RecurringDeposit',
    'CertificateOfDeposit'
  ) THEN NVL(
    (
      from_unixtime(
        unix_timestamp(
          cast(scratch_col_issue.date_string as date)
        ),
        'MM-dd-yyyy'
      )
    ),
    ' '
  ) ELSE '' END AS MAT_DTE
FROM
  scratch_col_issue
WHERE
  NVL(scratch_col_issue.id, '') IN (
      SELECT
        EXPLODE(
          SPLIT('8800', ',')
        )
  );

SELECT
  CASE WHEN scratch_col_issue.value in (
    'TermDeposit', 'RecurringDeposit',
    'CertificateOfDeposit'
  ) THEN NVL(
    (
      from_unixtime(
        unix_timestamp(
          cast(scratch_col_issue.date_string as date)
        ),
        'MM-dd-yyyy'
      )
    ),
    ' '
  ) ELSE '' END AS MAT_DTE
FROM
  scratch_col_issue
WHERE
  NVL(scratch_col_issue.id, '') IN (
      SELECT
        EXPLODE(
          SPLIT('8800', ',')
        )
  );