EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB438667: How to query binary data from Hive and Spark in MicroStrategy Secure Enterprise 10.x

David Currin

Quality Engineer, Senior • MicroStrategy

This article describes how users can query binary data from Hive and Spark in MicroStrategy 10.x, as normal SELECT queries will fail against binary datatypes

When choosing "Select Tables", "Build a Query", or "Type a Query" in the Data Import dialog in Strategy Web, query SQL will be automatically generated in the format "SELECT * FROM <TABLENAME>" or "SELECT <COLUMN> from <TABLENAME>".  If the tables or column(s) specified include columns in Hive or Spark with binary data, these SELECT queries will fail with an error.

ka02R000000kavJQAQ_0EM44000000Q07G.png

 


Error: FetchScroll failed. Error type: Odbc error. Odbc operation attempted: SQLFetchScroll. [HY000:35: on SQLHANDLE] [Simba][Hardy] (35) Error from server: error code: '0' error message: 'java.lang.ClassCastException'.)

CAUSE
This behavior is expected when accessing binary data via Hive or Spark connections.
WORKAROUND
To be able to select binary data, users can use built-in functions of Hive or Spark or create User-Defined Functions.  For example:
 

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT   base64(dt_binary)
FROM   wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
 
SELECT   c_to_string(dt_binary)
FROM   wh2.dt_fact_table1;

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT   base64(dt_binary)
FROM   wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
 
SELECT   c_to_string(dt_binary)
FROM   wh2.dt_fact_table1;

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT   base64(dt_binary)
FROM   wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
 
SELECT   c_to_string(dt_binary)
FROM   wh2.dt_fact_table1;

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT   base64(dt_binary)
FROM   wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
 
SELECT   c_to_string(dt_binary)
FROM   wh2.dt_fact_table1;

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT   base64(dt_binary)
FROM   wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
 
SELECT   c_to_string(dt_binary)
FROM   wh2.dt_fact_table1;

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT   base64(dt_binary)
FROM   wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
 
SELECT   c_to_string(dt_binary)
FROM   wh2.dt_fact_table1;

Built-in functions are available for all Hive or Spark databases, while User-Defined Functions must be explicitly defined before they can be used.
REFERENCE

  1. https://cwiki.apache.org/confluence/display/Hive/Binary+DataType+Proposal
  2. https://docs.datastax.com/en/datastax_enterprise/4.7/datastax_enterprise/ana/anaHivExDataType.html

The Strategy internal reference number for the issue discussed in this article is DE75476. KB438667

Comment

0 comments

Details

Knowledge Article

Published:

October 2, 2017

Last Updated:

December 28, 2018