Pentaho

 View Only
Expand all | Collapse all

Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

This thread has been viewed 32 times
  • 1.  Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-03-2022 12:23

    Hello,

    I designed transform to migrate data from MariaDB to AWS Arora Postgres using Pentaho community edition. While inserting data to target. Bit data type is taking as Boolean and insert fails on target tables.

    I am using below jar drivers.
    Source Maridb: mysql-connector-java-8.0.29.jar

    Target AWS Arora Postgres: postgresql-42.5.0.jar

    Error:

    2022/10/30 23:06:23 - Postgres Target Table Output.10 - Finished processing (I=0, O=0, R=9, W=0, U=0, E=1)2022/10/30 23:06:23 - Postgres Target Table Output.6 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2022/10/30 23:06:23 - Postgres Target Table Output.6 - Error updating batch
    2022/10/30 23:06:23 - Postgres Target Table Output.6 - Batch entry 0 INSERT INTO "hmsdev"."flyway_schema_history" ("installed_rank", "version", "description", "type", "script", "checksum", "installed_by", "installed_on", "execution_time", "success") VALUES ( 7, '7', 'create user table', 'SQL', 'V7__create_user_table.sql', -1638020935, 'u_G3rovgyxLE3TBA', '2019-04-11 15:58:14-04', 165, 'Y') was aborted: ERROR: column "success" is of type bit but expression is of type character varying
    Hint: You will need to rewrite or cast the expression.

    Source schema:
    CREATE TABLE `flyway_schema_history` (
    `installed_rank` int(11) NOT NULL,
    `version` varchar(50) DEFAULT NULL,
    `description` varchar(200) NOT NULL,
    `type` varchar(20) NOT NULL,
    `script` varchar(1000) NOT NULL,
    `checksum` int(11) DEFAULT NULL,
    `installed_by` varchar(100) NOT NULL,
    `installed_on` timestamp NOT NULL DEFAULT current_timestamp(),
    `execution_time` int(11) NOT NULL,
    `success` bit(1) NOT NULL,
    PRIMARY KEY (`installed_rank`),
    KEY `flyway_schema_history_s_idx` (`success`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


    Target Schema:

    CREATE TABLE hmsdev.flyway_schema_history
    (
    installed_rank integer NOT NULL,
    version character varying(50) COLLATE pg_catalog."default" DEFAULT 'NULL'::character varying,
    description character varying(200) COLLATE pg_catalog."default" NOT NULL,
    type character varying(20) COLLATE pg_catalog."default" NOT NULL,
    script character varying(1000) COLLATE pg_catalog."default" NOT NULL,
    checksum integer,
    installed_by character varying(100) COLLATE pg_catalog."default" NOT NULL,
    installed_on timestamp without time zone NOT NULL DEFAULT '1970-01-01 00:00:00'::timestamp without time zone,
    execution_time integer NOT NULL,
    success bit(1) NOT NULL
    )

    TABLESPACE pg_default;



    Please share your thoughts to fix it.

    Priyesh
    JPMChase


     



    ------------------------------
    Priyesh Patel
    Systems Engineer
    JPMorgan Chase
    ------------------------------


  • 2.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-03-2022 13:58
    Hi Priyesh,
    I think that PDI does not support bit(n) data type. Can you use boolean instead bit(1) on PostgreSQL server?

    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 3.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-04-2022 18:36
    Hi Petr,

    Appreciated your reply back. unfortunately on  target, app require similar data. Is there any alternative way to map bit data in to Pentaho? like using meta data injection or use java code before it insert data in to target?

    Thanks,
    Priyesh Patel
    JPMChase

    ------------------------------
    Priyesh Patel
    Systems Engineer
    JPMorgan Chase
    ------------------------------



  • 4.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-07-2022 07:27
    You can use step Execute SQL script as workaround. But this will be very slow because is not use batch mode for insert data.

    Example of configuration is in screen. You MUST cast field success as Integer via Select values step.


    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 5.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-08-2022 10:06
    Petr,
    Thanks for this options. We are migrating data for all tables so we can't construct insert statement and execute it separately.
    Also I do not see PostgreSQL bulk loader in Pentaho community edition.
    We are thinking create tables columns on Postgres target using boolean data type.
    thanks
    Priyesh Patel
    JPMChase.

    ------------------------------
    Priyesh Patel
    Systems Engineer
    JPMorgan Chase
    ------------------------------



  • 6.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-08-2022 10:19
    Which version of PDI are you using? PostgreSQL bulk loader is available in CE in Bulk loading section:


    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 7.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-08-2022 11:21

    Petr,

    Appreciated your quick reply. Below is my Pentaho version. 



    Thanks,

    Priyesh



    ------------------------------
    Priyesh Patel
    Systems Engineer
    JPMorgan Chase
    ------------------------------



  • 8.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-09-2022 02:50
    This is screen of job entries, not transformation steps.

    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 9.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-07-2022 08:18
    I was thinking of using a bulk loading step. There are fields of row stored as String and send to server. Step si PostgreSQL bulk loader.

    ------------------------------
    Petr Prochazka
    Systems Engineer
    P.V.A. systems s.r.o.
    ------------------------------



  • 10.  RE: Inserting data in to bit data type in AWS Arora Postgres using Pentaho community edition

    Posted 11-09-2022 14:49
    Petr,

    Right.. I was looking in job. I see Postgres bulk loader option in transform.
    I will try that and see.

    Thanks
    Priyesh
    JPMChase

    ------------------------------
    Priyesh Patel
    Systems Engineer
    JPMorgan Chase
    ------------------------------