Jaspersoft Hive Query Language

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.