Skip to main content

JDBC/HIVE Connection Properties

  • Updated

Previous Topic  Next Topic

JDBC/HIVE Connection Properties

The properties of a JDBC/HIVE connection are as follows:

Property NameDescription
Custom Query OptimizerSpecifies the implementation class of the QueryOptimizer interface for optimizing the query SQL statement before being sent to the DB. The value can be "Package_Name.Class_Name". If the class file has no package name, set the value to "Class_Name".

Once Custom Query Optimizer is specified,

Data type: String

Date FormatSpecifies the default Date format corresponding to the database.

Data type: String

DefaultSpecifies whether the connection is the default connection in the current data source. By default, the first added connection in a data source is the default connection of the data source. A data source can have zero or one default connection.

Data type: Boolean

DescriptionSpecifies the description of the JDBC connection.

Data type: String

DriverSpecifies the class name of the JDBC driver such as oracle.jdbc.driver.OracleDriver. While setting up the JDBC connection with the connection dialog, Logi JReport will use the driver that is specified to connect to the database. If no driver name is filled in, Logi JReport will use the default JDBC driver from the file jdbcdrivers.properties in <install_root>\bin file. You can add JDBC driver names into the text file, Logi JReport will load the drivers before building a connection. If your JDBC driver name is not correct, or you do not add JDBC driver names, the message "No suitable driver" will appear.

Data type: String

Explicit Inner JoinSpecifies whether to use Explicit Join notation or Implicit Join notation in the Where clause for inner joins. For HIVE connection, it can only be true.
  • true - Uses Explicit Join notation:

    SQL: select … from A inner join B on (A.c1 = B.c2)

  • false - Uses Implicit Join notation:

    SQL: select … from A,B where A.c1 = B.c2

Data type: Boolean

Included SchemasDisplays the schema names specified for the connection in the Get JDBC Connection Information dialog. The default is blank, which indicates that all schemas in the DBMS can be used in the catalog. This property is read only. To modify it, use the Get JDBC Connection Information dialog.
NameSpecifies the name of the connection which, by default, is the same as the connection URL, but can also be a user friendly name for the connection.

If the name has already been used, a number starting from 1 will be appended to the name. For example, if aa exists, the new name will be aa1, and if both aa and aa1 exits, it will be aa2, and so on. If you change the name, the imported SQL and stored procedures that reference the connection will be automatically updated to use the new name.

Data type: String

Name PatternSpecifies whether or not catalog or schema is used in data manipulation. Choose an option from the drop-down list.
  • unqualified name - Neither catalog nor schema is included in data manipulation. Example: SELECT t.c FROM t
  • 2-part names - Uses schema in data manipulation. Example: SELECT schema.t.c FROM schema.t
  • 3-part names - Uses both catalog and schema in data manipulation. Example: SELECT catalog.schema.t.c from catalog.schema.t

Data type: Enumeration

Outer Join Marker Specifies the behavior of the outer joins in the connection. Choose an option from the drop-down list.
  • SQL92 - Specifies to use the SQL92 standard.
  • + - Specifies to use Oracle's "+=+" standard.

Data type: Enumeration

PasswordSpecifies the password for connecting with the database, which is determined by the database DBA.

Data type: String

Push Down Group QuerySpecifies whether to push down group level summary computations in reports to the DBMS at runtime. Choose an option from the drop-down list. For HIVE connection, it can only be true.
  • true - Logi JReport will push down the group level summary computations to the DBMS if the DBMS can do the computations, otherwise, Logi JReport will do the computations itself.
  • false - Logi JReport will do the group level summary computations itself.

Data type: Boolean

Quote QualifierSpecifies the characters, then a qualifier name which contains the characters that will not be quoted. Choose an option from the drop-down list.
  • Default (JDBC) - If selected, the program will get the extra name characters from JDBC.
  • User Defined - If selected, you can modify the quote character according to the database system being used.

Data type: Enumeration

Read OnlySpecifies the mode to open the connection to the JDBC data source. The initial setting is default which uses the mode specified by the DBMS Administrator which could be read only or read & write. Choose an option from the drop-down list.
  • default - May be read & write or read only depending on the DBMS default setting.
  • read only - Allows the driver to optimize performance for reporting which does not need to write to the DBMS.
  • read & write - Opens the DBMS with updates enabled which requires more processing to ensure concurrency control.

Data type: Enumeration

Security CheckSpecifies whether or not to check the JDBC connection security at runtime.

Data type: Boolean

SQL Statement CreatorSpecifies the parameters to implement the SQLStmtCreator interface (for details about the interface and its usage, refer to Dynamic Queries).

Data type: String

Time FormatSpecifies the default Time format corresponding to the database.

Data type: String

Timestamp FormatSpecifies the default Timestamp format corresponding to the database.

Data type: String

Transaction ModeSpecifies the transaction mode for the connection. Choose an option from the drop-down list.
  • default - Indicates the transaction information cannot be got from JDBC connection.
  • none - Indicates that transactions are not supported.
  • read uncommitted - Dirty reads, non-repeatable reads and phantom reads can occur. This mode will speed up the transaction of the catalog.
  • read committed - Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
  • repeatable read - Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
  • serializable - Dirty reads, non-repeatable reads and phantom reads are prevented.

Data type: Enumeration

URLSpecifies the JDBC URL which establishes the connection to the database, for example jdbc:oracle:thin:@localhost:1521:ORCL.

Data type: String

UserSpecifies the user name for connecting to the database, which is determined by the database DBA.

Data type: String

Timestamp Format and Date Format

SymbolMeaningPresentation
yyearNumber
MmonthNumber
dday in monthNumber
Hhour in day (0~23)Number
hhour in am/pm (1~12)Number
mminute in hourNumber
ssecond in minuteNumber
SmillisecondNumber

Example (using the US Locale):

"yyyyy.MMMMM.dd hh:mm aaa" ->> 1996.July.10 12:08 PM

Note: The Date and Timestamp format Logi JReport supports follows that of Java. Refer to the Java API Specification java.text package DateFormat interface.

Push Down Group Query

This property is to specify whether to push down group level summary computations in reports to the DBMS at runtime. The group level summary computations can be pushed down to the DBMS when the aggregate function is Count, Sum, Maximum, Minimum, or Square Sum. The aggregate functions Average, PopulationStdDev, PopulationVariance, StdDev and Variance are not pushed down, but instead computed by Logi JReport using the results of the pushed down functions. By pushing down the group level summary computations, the DBMS' computation capability can be made use of, and thus the reports' running efficiency will be improved.

The property can be specified at three levels for page report: JDBC connection, query within the connection, and individual page report. The setting on page report has the highest priority. While, for web reports and library components, the property can be specified only on the JDBC connection or the query within the connection, and the setting on the query has higher priority. Since there is no affect on data returned when detail data is needed, it is recommended to set it on every JDBC connection.

After you activate Push Down Group Query, Logi JReport will generate aggregate functions and GROUP BY statements for any data components that only view aggregated results. This includes all charts, crosstabs, summary tables and banded objects which hide the detail panel. When Logi JReport retrieves the data for one of these types of data components, it dynamically modifies the SQL to only return the aggregated data. Thus you can use the same generic query to run many different data components without modifying the query. For example, a chart based on table Orders that shows sales by state will generate a query that just returns one row per state while a summary table by product name will return just one record for each product. This can result in orders of magnitude better performance.

The QueryOptimizer Interface

The QueryOptimizer interface is contained in the package toolkit.db.queryoptimization. See Logi JReport Javadoc in <install_root>\help\api for usage of the interface.

This interface contains only one method:

Optimizer optimizeQuery(QueryInfo queryInfo);

One QueryInfo will be automatically passed into the interface, and one Optimizer object should be returned.

To implement the interface and make it work:

  1. Define a Java class file that implements the QueryOptimizer interface.
  2. Compile the Java file to generate the class file.
  3. Append the class path to the ADDCLASSPATH variable of the file setenv.bat in <install_root>\bin.
  4. Start Logi JReport Designer with the modified batch file.
  5. Open the catalog that contains the connection you would like to optimize.
  6. Set the Custom Query Optimizer property of the connection to "Package_Name.Class_Name".

Note: The List<ColumnInfo> parameter in the implementation class of the QueryOptimizer interface depends on the Prefetch property of the related business view. When Prefetch is true, List<ColumnInfo> will include all columns in the business view, otherwise, it will only include columns used by the related report.

Previous Topic  Next Topic

Related articles

Comments

0 comments

Please sign in to leave a comment.