Member since
07-19-2023
3
Posts
1
Kudos Received
0
Solutions
07-20-2023
06:31 AM
Thanks for your suggestion. Power BI uses ODBC connector to retrieve data in Cloudera CDP; from the documentation "In Impala 2.0 and later, you can set query options directly through the JDBC and ODBC interfaces by using the SET statement." (https://impala.apache.org/docs/build/html/topics/impala_query_options.html ), and my IMPALA version is "3.2.0-cdh6.3.4". Therefore, Power BI is supposed to be able to run the query option statement... However, I will still consult the Power BI end, as you suggested.
... View more
07-20-2023
01:23 AM
Hello! I am creating a view `view_business` with IMPALA: since the source table `table_a` is a parquet formatted table with complex data types such as array and struct, I have to always run an query option statement "SET PARQUET_ARRAY_RESOLUTION=TWO_LEVEL_THEN_THREE_LEVEL" to load the arrays; otherwise those array columns are showing NULL value. DROP VIEW IF EXISTS discovery_database.view_business;
SET PARQUET_ARRAY_RESOLUTION=TWO_LEVEL_THEN_THREE_LEVEL;
CREATE VIEW discovery_database.view_business AS (
SELECT * FROM table_a
) Once the view is created, if I want to query the view, I still need to run the query option statement first as follows: SET PARQUET_ARRAY_RESOLUTION=TWO_LEVEL_THEN_THREE_LEVEL;
SELECT * FROM discovery_database.view_business However, the query option statement above is not supported in ODBC connector when I tried to use the statements above to link the curated table `view_business` as a data source in a power BI report. My question is: is it possible to create a view INCLUDING the query option statement "SET PARQUET_ARRAY_RESOLUTION=TWO_LEVEL_THEN_THREE_LEVEL", so every time when I query the view, I don't need to run this statement any more? Thank you very much in advance! Please note that IMPALA does not support scheduled query (to create a table instead a view). I also don't have a access right to create a scheduled query with HIVE in the HUE console. Therefore, creating a table with scheduled query cannot be the solution...
... View more
Labels:
07-19-2023
12:59 AM
1 Kudo
Hello, is there any one know how to set a query option in ODBC connector in Power BI? Basically, my SQL query contains 2 statements: (1) Firstly set a query option to allow the array columns in a PARQUET formatted table to be retrieved SET PARQUET_ARRAY_RESOLUTION=TWO_LEVEL_THEN_THREE_LEVEL (2) Then query the table. Without the first statement, all the array columns will appear NULL value. Therefore, the first statement is necessary. The 2 statements work properly in HUE SQL console with IMPALA SQL. However it results error in ODBC connector, and it seems that ODBC doesn't allow to set a query option: I mean if I remove the query option "SET PARQUET_ARRAY_RESOLUTION=TWO_LEVEL_THEN_THREE_LEVEL;", the "SELECT" statement simply works... Do you know how to resolve this issue? Thank you in advance!
... View more
Labels: