Concat/cast function while using Impala

AJ16 — Tue, 13 Aug 2024 21:26:35 GMT

Hello Experts,

I'm querying a DB in Hadoop and need to combine the date and time fields within the query itself. I will then use the combined field to join to another Hadoop table but I'm not able to use cast and concat correctly:

Data available in DB:

Dt(string): 2024-06-14

Tm (string): 040507669747

Output that I'm trying to create:

Dat_time: 20240614040507669747

My query:

proc sql;

connect to impala (dsn=abc user="&sysuserid." pw="&password.");

create table abc as (

select id, dt, tm,

concat(cast(date_format(dt,'yyyymmdd') as string), tm) as dat_time /*I tried creating this from what I've learned so far but it's giving error*/

from DB);

disconnect from impala;

quit;

Error: [Cloudera][Impala ODBC (370) Query analysis error occurred during query execution: [HY000]: AnalysisException: default.date_format() unknown for database default. Currently this db has 0 functions.

I'm unable to figure the correct syntax to make this happen. Would appreciate any guidance.

Thank you!

Re: Concat/cast function while using Impala

Kurt_Bremser — Wed, 14 Aug 2024 06:09:36 GMT

What do you get in SAS when you do

libname xx ... /* connection to the DB */;

data abc;
set xx.db;
keep id dt tm;
run;

What types and formats do dt and tm have, and please show some values.

Re: Concat/cast function while using Impala

LinusH — Thu, 15 Aug 2024 11:11:45 GMT

Since it sems that you have problems with Hadoop/Impala syntax, it would make sense to reach out to such a forum instead.

topic Re: Concat/cast function while using Impala in Developers

Concat/cast function while using Impala

Re: Concat/cast function while using Impala

Re: Concat/cast function while using Impala