Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Assigning a table name to a variable in HUE- Impala Editor

avatar
New Contributor

Hello,

 

I am trying to assighn table names to variable in Impala editor on HUE but it does not seem to work.

 

set var:table_name=production_table;

 

select * from ${var:table_name}

 

I get the below error, when I execute the below set statement.

 

set var:table_name=production_table;

 

  • AnalysisException: Syntax error in line 1:undefined: set var:table_name=production_table ^ Encountered: : Expected: ADD, AND, ARRAY, AS, ASC, BETWEEN, BIGINT, BINARY, BLOCK_SIZE, BOOLEAN, CACHED, CASCADE, CHANGE, CHAR, COMMENT, COMPRESSION, CROSS, DATE, DATETIME, DECIMAL, DEFAULT, DESC, DIV, REAL, DROP, ELSE, ENCODING, END, FLOAT, FOLLOWING, FROM, FULL, GROUP, IGNORE, HAVING, ILIKE, IN, INNER, INTEGER, IREGEXP, IS, JOIN, LEFT, LIKE, LIMIT, LOCATION, MAP, NOT, NULL, NULLS, OFFSET, ON, OR, ORDER, PARTITION, PARTITIONED, PRECEDING, PRIMARY, PURGE, RANGE, RECOVER, REGEXP, RENAME, REPLACE, RESTRICT, RIGHT, RLIKE, ROW, ROWS, SELECT, SET, SMALLINT, SORT, STORED, STRAIGHT_JOIN, STRING, STRUCT, TABLESAMPLE, TBLPROPERTIES, THEN, TIMESTAMP, TINYINT, TO, UNCACHED, UNION, USING, VALUES, VARCHAR, WHEN, WHERE, WITH, COMMA, IDENTIFIER CAUSED BY: Exception: Syntax error
  • Operation has no valid handle attached
5 REPLIES 5

avatar
New Contributor

Meltem_0-1607534463134.png

Hi @sai, you can use this script.

For id column, 2 is default value. You don't have to define default value, its optional.

avatar
New Contributor

¿How is it possible to set the value using a query not a fixed value? For example, if I have a select that retunrs a date, how can I add to a variable?

avatar
New Contributor

Hi Steven9, is posible if you use as follow:

 

/* DROP, CREATE AND INSERT */
DROP IF EXIST YOUR_LIBRARY.YOUR_TABLE;
CREATE TABLE IF NOT EXISTS YOUR_LIBRARY.YOUR_TABLE(Ciudad VARCHAR(50), Valor BIGINT);
INSERT INTO TABLE YOUR_LIBRARY.YOUR_TABLE VALUES
(CAST("BOGOTA" AS VARCHAR(50)), 1000),
(CAST("CARTAGENA" AS VARCHAR(50)), 500),
(CAST("MEDELLIN" AS VARCHAR(50)), 50),
(CAST("PURIFICACION" AS VARCHAR(50)), 600),
(CAST("DOLORES" AS VARCHAR(50)), 250)
(CAST("CALI" AS VARCHAR(50)), 450),
;

/* VARIABLES */
${TRX=500};
${CITY="MEDELLIN"};

/* USING VARIABLES TO EXTRACT FROM TABLE */
SELECT * FROM YOUR_LIBRARY.YOUR_TABLE WHERE VALOR >= ${TRX};
SELECT * FROM YOUR_LIBRARY.YOUR_TABLE WHERE CIUDAD <> ${CITY};

 

avatar
New Contributor

DROP TABLE IF EXISTS...

avatar
New Contributor

Hi Sai, is possible if you use as follow 

 

/* DROP, CREATE AND INSERT */
DROP IF EXIST YOUR_LIBRARY.YOUR_TABLE;
CREATE TABLE IF NOT EXISTS YOUR_LIBRARY.YOUR_TABLE(Ciudad VARCHAR(50), Valor BIGINT);
INSERT INTO TABLE YOUR_LIBRARY.YOUR_TABLE VALUES
(CAST("BOGOTA" AS VARCHAR(50)), 1000),
(CAST("CARTAGENA" AS VARCHAR(50)), 500),
(CAST("MEDELLIN" AS VARCHAR(50)), 50),
(CAST("PURIFICACION" AS VARCHAR(50)), 600),
(CAST("DOLORES" AS VARCHAR(50)), 250)
(CAST("CALI" AS VARCHAR(50)), 450),
;

/* VARIABLES */
${TRX=500};
${CITY="MEDELLIN"};

/* USING VARIABLES TO EXTRACT FROM TABLE */
SELECT * FROM YOUR_LIBRARY.YOUR_TABLE WHERE VALOR >= ${TRX};
SELECT * FROM YOUR_LIBRARY.YOUR_TABLE WHERE CIUDAD <> ${CITY};