Define Native (JDBC) Database Connections
来源:互联网 发布:搜了网络股票代码 编辑:程序博客网 时间:2024/06/08 08:48
Define Native (JDBC) Database Connections
- Add Drivers
- Create Connections
- Add Database-Specific Options
- Configure Database Connections
- Define Connection Pooling
- Connect to Clusters
- Modify Connections
Add Drivers
The DI Server and workstations need the appropriate driver to connect to the database that stores your data. Your database administrator, Chief Intelligence Officer, or IT manager should be able to provide the appropriate driver. If not, you can download drivers from your database vendor's website. See the Supported Technologies to ensure that your database and its driver are supported by Pentaho.
Installing Drivers
Once you have the correct driver, copy it to these directories.
DI Server: /pentaho/server/data-integration-server/tomcat/webapps/pentaho-di/WEB-INF/lib/.
Spoon: data-integration/lib
You must restart Spoon for the driver to take effect.
There should be only one driver for your database in this directory. Ensure that there are no other versions of the same vendor's driver in this directory. If there are, back up the old driver files and remove them to avoid version conflicts. This is a concern when you are adding a driver for the same database type as your Pentaho solution repository. If you have any concerns about how to proceed, contactPentaho support.
When the driver files are in place restart the server.
Connecting to a Microsoft SQL Server Using Integrated or Windows Authentication
If you are using a Microsoft SQL Server (MSSQL), you might need to use an alternative, non-vendor-supported driver called JTDS. ContactPentaho support to ensure that you are adding the correct driver
For Microsoft Windows, most JDBC drivers support Type 2 integrated authentication through theintegratedSecurity connection string property. To use integrated authentication, copy thesqljdbc_auth.dll file to all machines and directories to which you copied the JDBC driver. You can find this file in this location.
<installation directory>\sqljdbc_<version>\<language>\auth\
Specify Native (JDBC) Connection Information
Before you can create the connection, you must have installed the appropriate JDBC driver for your particular data.
Pentaho Data Integration (PDI) allows you to define connections to multiple databases provided by multiple database vendors (MySQL, Oracle, PostgreSQL, and many more). PDI ships with the most suitable JDBC drivers for PostgreSQL, our default database.
Pentaho recommends that you avoid using ODBC connections. The ODBC to JDBC bridge driver does not always provide an exact match and adds another level of complexity that may affect performance. The only time you may have to use ODBC is if there is no available JDBC driver. For details, this article explains "Why you should avoid ODBC."http://wiki.pentaho.com/pages/viewpage.action?pageId=14850644.
When you define a database connection, the connection information (for example, the user name, password, and port number) is stored in the DI Repository and is available to other users when they connect to the repository. If you are not using the DI Repository, the database connection information is stored in the XML file associated with the transformation or job.
Connections that are available for use with a transformation or job are listed under theDatabase connections node in the View pane in Spoon.
- From within Spoon, navigate to theView tab of the Explorer pane. Double-click on theDatabase connections folder. The Database Connection dialog box appears.
Section Name What to Do Connection NameType name that uniquely identifies your new connectionConnection TypeSelect the type of database to which you are connectingAccessSelect your method of access. Available access types depend on the connecting database type.Host NameType the name of the server that hosts the database to which you are connecting. Alternatively, you can specify the host by IP address.Database NameEnter the name of the database to which you are connecting. If you are using a ODBC connection, enter the Data Source Name (DSN) in this field.Port NumberEnter the TCP/IP port number if it is different from the default.User nameOptionally, type the user name used to connect to the database.PasswordOptionally, type the password used to connect to the database. - Click Test.A confirmation message displays if Spoon is able to establish a connection with the target database.
- Click OK to save your entries and exit the Database Connection dialog box.
- From within the View tab, right-click on the connection and selectShare from the list that appears. This shares the connection with your users. They will be able to select the shared connection. From within theView tab, click Explore to open theDatabase Explorer for an existing connection. This shows you the schemas and tables inside the connection.
Add Database-Specific Options
Add database-specific options by adding parameters to the generated URL.
- From within the Database Connection dialog box, select Options.
- Select the first available row in the parameter table.
- Choose the database type and enter a valid parameter name and its corresponding value.Note: For more database-specific configuration help, clickHelp. A new browser opens and displays additional information about configuring the JDBC connection for the currently selected database type.
- Click OK to save your entries.
Advanced Configuration of Database Connections
The Advanced option in the Database Connection dialog box allows you to configure properties that are, for most part, associated with how SQL is generated. These options allow you to set a standard across all of your SQL tools, ETL tools and design tools. All database table names and column names are always upper case or lower case no matter what users do in the tools.
Pentaho has implemented a database-specific quoting system that allows you to use any name or character acceptable to the supported databases' naming conventions.
Pentaho Data Integration contains a list of reserved words for most of the supported databases. To ensure that quoting behaves correctly, Pentaho has implemented a strict separation between the schema (user/owner) of a table and the table name itself. Doing otherwise makes it impossible to quote tables or fields with one or more periods in them correctly. Placing periods in table and field names is common practice in some ERP systems (for example, fields such as "V.A.T.")
To avoid quoting-related errors, a rule stops the Pentaho Data Integration from performing quoting activity when there is a start or end quote in the table name or schema. This allows you to specify the quoting mechanism yourself.
Define Connection Pooling
Instead of having a connection open for each individual step in a transformation, you can set up a connection pool and define options like the initial pool size, maximum pool size, and connection pool parameters. For example, you might start with a pool of ten or fifteen connections, and as you run jobs or transformations, the unused connections drop off. Pooling helps control database access, especially if you have transformations that contain many steps and that require a large number of connections. Pooling can also be implemented when your database licensing restricts the number of active concurrent connections.
This table shows descriptions of the pooling options.
- Select Enable Connection Pooling.
- Type the initial pool size in the Initial: area and the maximum pool size in the Maximum: area.
- Select the parameters you need from within theParameters: area. A Description of the parameter appears in theDescription: area when you select a check box.
- Click OK to save your selections and close theDatabase Connection dialog box.
Connect to Clusters
Modify Connections
This table contains information about other database-related connection tasks you can perform.
- Define Native (JDBC) Database Connections
- JDBC出现这个错误的解决办法Connections could not be acquired from the underlying database!
- Java JDBC Connections
- Eclipse中配置Database Connections
- Java:jdbc Too many connections
- Kill All Active Connections To A Database.
- "mysql database connections will be migrated"
- spring jdbc:initialize-database
- JDBC 之 连接DataBase
- Connections could not be acquired from the underlying database!
- 解决:Connections could not be acquired from the underlying database
- Connections could not be acquired from the underlying database!
- Nginx + PHP mysql_pconnect = Database errors (Too many connections)
- 解决:Connections could not be acquired from the underlying database!
- Connections could not be acquired from the underlying database
- Connections could not be acquired from the underlying database
- 解决:Connections could not be acquired from the underlying database!
- Connections could not be acquired from the underlying database
- 第五周项目1-3三角形类的构造函数
- This version of the rendering library is more recent than your version of ADT plug-in. Please update
- 使用SuperSlide制作首页轮播图
- 基于 GNOME2 桌面的 GConf 配置系统的介绍
- maven下nexus的搭建,jar包上傳等注意事項(linux)
- Define Native (JDBC) Database Connections
- FEKO5.5安装破解方法
- 《C++ Primer第五版》学习笔记-插播-模板偏特化、编译期Assertions与Type2Type
- SourceMonitor的复杂度计算方法
- 公司渠道管理
- hmtl5find(‘threaded’)案例
- os
- VS 无法解析的外部符号”[错误]
- 图像传感器之CMOS(1)