Jaspersoft Hive Query Language

SELECT name, phone_office           , billing_address_city           , billing_address_street           , billing_address_country  FROM accounts[/code]

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[/code]

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[/code]

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[/code]

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}[/code]

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[/code]

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.