edit-icon download-icon

Configure RDBMS Writer

Last Updated: Mar 21, 2018

Overview

The RDBMSWriter plug‑in provides the ability to write data into the target table of the master RDBMS database. At the underlying implementation level, RDBMSWriter connects to a remote RDBMS database through JDBC, and runs the “insert into…” SQL statement to write data into RDBMS. RDBMSWriter is a common plug-in for writing data into relational databases, and you can add any write support for relational databases by registering a database driver.

RDBMSWriter is designed for ETL developers to import data from data warehouses to RDBMS. RDBMSWriter can also be used as a data migration tool by DBA and other users.

How it works

RDBMSWriter obtains the protocol data generated by Reader by using the data synchronization framework. Then it connects to a remote RDBMS database through JDBC, and runs the “insert into…” SQL statement to write data into RDBMS.

Features

Configuration samples

Configure a job for writing data into RDBMS.

  1. {
  2. "job": {
  3. "setting": {
  4. "speed": {
  5. "channel": 1
  6. }
  7. },
  8. "content": [
  9. {
  10. "reader": {
  11. "name": "streamreader",
  12. "parameter": {
  13. "column": [
  14. {
  15. "value": "DataX",
  16. "type": "string"
  17. },
  18. {
  19. "value": 19880808,
  20. "type": "long"
  21. },
  22. {
  23. "value": "1988-08-08 08:08:08",
  24. "type": "date"
  25. },
  26. {
  27. "value": true,
  28. "type": "bool"
  29. },
  30. {
  31. "value": "test",
  32. "type": "bytes"
  33. }
  34. ],
  35. "sliceRecordCount": 1000
  36. }
  37. },
  38. "writer": {
  39. "name": "rdbmswriter",
  40. "parameter": {
  41. "connection": [
  42. {
  43. "jdbcUrl": "jdbc:dm://ip:port/database",
  44. "table": [
  45. "table"
  46. ]
  47. }
  48. ],
  49. "username": "username",
  50. "password": "password",
  51. "table": "table",
  52. "column": [
  53. "*"
  54. ],
  55. "preSql": [
  56. "delete from XXX;"
  57. ]
  58. }
  59. }
  60. }
  61. ]
  62. }
  63. }

Parameter description

  • jdbcUrl

    • Description: Information of the JDBC connection to the opposite-end database. The format of jdbcUrl is in accordance with the RDBMS official specification, and the URL attachment control information can be entered. The JDBC format varies with different databases. DataX selects an appropriate database driver for data reading based on the specific JDBC format.

      • DM jdbc:dm://ip:port/database
      • db2 jdbc:db2://ip:port/database
      • PPAS jdbc:edb://ip:port/database

      How to add new supported database with rdbmswriter:

      • Go to the rdbmswriter directory. The ${DATAX_HOME} here is the home directory of DataX. ${DATAX_HOME}/plugin/writer/rdbmswriter
      • Under the rdbmswriter directory, you can find the plugin.json configuration file. Use this file to register your specific database driver, which is placed in the drivers array. The RDBMSWriter plug-in dynamically selects the appropriate database driver to connect to the database when running the job.
      1. {
      2. "name": "rdbmswriter",
      3. "class": "com.alibaba.datax.plugin.reader.rdbmswriter.RdbmsWriter",
      4. "description": "useScene: prod. mechanism: Jdbc connection using the database, execute select sql, retrieve data from the ResultSet. warn: The more you know about the database, the less problems you encounter.",
      5. "developer": "alibaba",
      6. "drivers": [
      7. "dm.jdbc.driver.DmDriver",
      8. "com.ibm.db2.jcc.DB2Driver",
      9. "com.sybase.jdbc3.jdbc.SybDriver",
      10. "com.edb.Driver"
      11. ]
      12. }
      • The rdbmswriter directory contains the libs sub-directory, under which you must put your specific database driver.
      1. $tree
      2. .
      3. |-- libs
      4. | |-- Dm7JdbcDriver16.jar
      5. | |-- commons-collections-3.0.jar
      6. | |-- commons-io-2.4.jar
      7. | |-- commons-lang3-3.3.2.jar
      8. | |-- commons-math3-3.1.1.jar
      9. | |-- datax-common-0.0.1-SNAPSHOT.jar
      10. | |-- datax-service-face-1.0.23-20160120.024328-1.jar
      11. | |-- db2jcc4.jar
      12. | |-- druid-1.0.15.jar
      13. | |-- edb-jdbc16.jar
      14. | |-- fastjson-1.1.46.sec01.jar
      15. | |-- guava-r05.jar
      16. | |-- hamcrest-core-1.3.jar
      17. | |-- jconn3-1.0.0-SNAPSHOT.jar
      18. | |-- logback-classic-1.0.13.jar
      19. | |-- logback-core-1.0.13.jar
      20. | |-- plugin-rdbms-util-0.0.1-SNAPSHOT.jar
      21. | `-- slf4j-api-1.7.10.jar
      22. |-- plugin.json
      23. |-- plugin_job_template.json
      24. `-- rdbmswriter-0.0.1-SNAPSHOT.jar
    • Required: Yes
    • Default value: None
  • username

    • Description: Username for the data source.
    • Required: Yes
    • Default value: None
  • password

    • Description: Password corresponding to the specified username for the data source.
    • Required: Yes
    • Default value: None
  • table

    • Description: Target table name. If the schema information of table is not consistent with the username in the preceding configuration, enter the table information in the schema.table format.
    • Required: Yes
    • Default value: None
  • column

    • Description: The column name set to be synchronized in the configured table, separated by commas (,).It is not recommended to use the default columns.
    • Required: Yes
    • Default value: None
  • preSql

    • Description: The SQL statement that is run before the data synchronization task is run. Currently, you can run only one SQL statement, for example, clear old data.
    • Required: No
    • Default value: None
  • postSql

    • Description: The SQL statement that is run before the data synchronization task is run. Currently, you can run only one SQL statement, for example, add a timestamp.
    • Required: No
    • Default value: None
  • batchSize

    • Description: The quantity of records submitted in one operation. Setting this parameter can greatly reduce the interactions between DataX and RDBMS over the network, and increase the overall throughput. However, an excessively large value may cause the running process of DataX to become out of memory (OOM).
    • Required: No
    • Default value: 1024

Type conversion

RDBMSReader supports most general-purpose relational database types such as numbers and characters. Make your decision based on your specific database type.

Thank you! We've received your feedback.