Jaspersoft Hive Query Language

Hive Query Language

The Jaspersoft Hadoop Hive connector uses HiveQL which is the standard query language for Hive. Any valid HiveQL query may be used in a report. The language is documented here:

Sample queries

HiveQL looks just like SQL in most cases.

Basic query

SELECT name, phone_office
           , billing_address_city
           , billing_address_street
           , billing_address_country
  FROM accounts

Parameterized as a prepared statement (new in Hive 0.8)

-- Parameter $P{COUNTRY} is a String
  SELECT name, phone_office
             , billing_address_city
             , billing_address_street
             , billing_address_country
    FROM accounts 
   WHERE billing_address_country = $P{COUNTRY} 
ORDER BY billing_address_country
             , billing_address_city

Parameterized using string replacement (not a prepared statement)

  SELECT name, phone_office
             , billing_address_city
             , billing_address_street
             , billing_address_country 
    FROM accounts 
   WHERE billing_address_country = $P!{COUNTRY} 
ORDER BY billing_address_country
             , billing_address_city

Parameterized IN clause

-- Parameter $P{COUNTRY} is a Collection
  SELECT name, phone_office
             , billing_address_city
             , billing_address_street
             , billing_address_country
    FROM accounts 
   WHERE $X{IN, billing_address_country, COUNTRY} 
ORDER BY billing_address_country
             , billing_address_city

Limit to N rows where N can be specified by the user as a parameter

  SELECT name, phone_office
             , billing_address_city
             , billing_address_street
             , billing_address_country 
    FROM accounts 
ORDER BY billing_address_country
             , billing_address_city
   LIMIT $P{LIMIT}

Exploding HiveQL (not SQL, but very cool!)

      SELECT word, COUNT(word) AS word_count 
        FROM sample_text_table 
LATERAL VIEW EXPLODE(line) words AS word 
    GROUP BY word

Hive and the Jaspersoft Metadata Layer

The Hive JDBC driver does not yet fully implement the JDBC specifications, and it does not work correctly with the Jaspersoft metadata layer (Data Domains) yet. Specifically, Jaspersoft requires these unimplemented methods:

  • java.sql.DatabaseMetaData.getIdentifierQuoteString()
  • java.sql.DatabaseMetaData.storesUpperCaseIdentifiers()
  • java.sql.DatabaseMetaData.getImportedKeys

As a result of this, Ad Hoc reporting in JasperReports Server commercial editions against Hive data sources must use Topics rather than Domains. Support for Hive in the Jaspersoft metadata layer is planned for a future release.

Feedback
randomness