search

embulk / embulk-output-jdbc

MySQL, PostgreSQL, Redshift and generic JDBC output plugins for Embulk
Other
88 stars 86 forks source link

"UTC" is recognized as "Z" to be compatible with the legacy style. #331

Open KrmBngL opened 9 months ago

KrmBngL commented 9 months ago

While transferring data from SQL Server to PostgreSQL with Embulk, I get an error as follows.

2023-12-04 19:07:08.875 +0300 [INFO] (0001:transaction): Loaded plugin embulk-input-sqlserver (0.13.2) 2023-12-04 19:07:08.939 +0300 [INFO] (0001:transaction): Loaded plugin embulk-output-postgresql (0.10.3) 2023-12-04 19:07:09.048 +0300 [WARN] (0001:transaction): "UTC" is recognized as "Z" to be compatible with the legacy style. . . . 2023-12-04 19:07:10.042 +0300 [INFO] (0001:transaction): > 0.00 seconds 2023-12-04 19:07:10.090 +0300 [INFO] (0001:transaction): {done: 0 / 1, running: 0} 2023-12-04 19:07:10.117 +0300 [WARN] (0018:task-0000): Z is deprecated as a military time zone name. Use UTC instead. 2023-12-04 19:07:10.122 +0300 [WARN] (0018:task-0000): "Z" is recognized as "Z" to be compatible with the legacy style.

My embulk version: embulk 0.8.38 My postgresql version: 15.4 My java version: 1.8

This is the list of gems in the embulk gem list. embulk-input-sqlserver (0.13.2 java) embulk-output-postgresql (0.10.3 java, 0.8.7)

hiroyuki-sato commented 9 months ago

Hello, @KrmBngL Did you post the same question on the Stack overflow?

When you ask a question, write your environment information like

If you ask about the error org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: java.lang.NoSuchMethodError: org.embulk.spi.time.Timestamp.ofInstant(Ljava/time/Instant;)Lorg/embulk/spi/time/Timestamp;, I suspect you are using too old embulk version, Could you try 0.9.25 or 0.11.2.

When you use 0.11.2, you need few steps (ex. setup JRuby) https://www.embulk.org/articles/2023/04/13/embulk-v0.11-is-coming-soon.html

KrmBngL commented 9 months ago

Hi @hiroyuki-sato thank you for information. I tried 0.9.25 on another vm. But im getting this error in this version when i run .yml file 2023-12-06 00:56:13.963 +0300: Embulk v0.9.25 2023-12-06 00:56:14.695 +0300 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected. 2023-12-06 00:56:16.084 +0300 [INFO] (main): Gem's home and path are set by default: "/root/.embulk/lib/gems" while scanning a simple key in 'string', line 61, column 3: mode : truncate_insert ^ could not find expected ':' in 'string', line 62, column 1:

^

    at org.yaml.snakeyaml.scanner.ScannerImpl.stalePossibleSimpleKeys(ScannerImpl.java:465)
    at org.yaml.snakeyaml.scanner.ScannerImpl.needMoreTokens(ScannerImpl.java:280)
    at org.yaml.snakeyaml.scanner.ScannerImpl.checkToken(ScannerImpl.java:225)
    at org.yaml.snakeyaml.parser.ParserImpl$ParseBlockMappingKey.produce(ParserImpl.java:557)
    at org.yaml.snakeyaml.parser.ParserImpl.peekEvent(ParserImpl.java:157)
    at org.yaml.snakeyaml.parser.ParserImpl.checkEvent(ParserImpl.java:147)
    at org.yaml.snakeyaml.composer.Composer.composeMappingNode(Composer.java:224)
    at org.yaml.snakeyaml.composer.Composer.composeNode(Composer.java:154)
    at org.yaml.snakeyaml.composer.Composer.composeValueNode(Composer.java:246)
    at org.yaml.snakeyaml.composer.Composer.composeMappingChildren(Composer.java:237)
    at org.yaml.snakeyaml.composer.Composer.composeMappingNode(Composer.java:225)
    at org.yaml.snakeyaml.composer.Composer.composeNode(Composer.java:154)
    at org.yaml.snakeyaml.composer.Composer.composeDocument(Composer.java:122)
    at org.yaml.snakeyaml.composer.Composer.getSingleNode(Composer.java:105)
    at org.yaml.snakeyaml.constructor.BaseConstructor.getSingleData(BaseConstructor.java:122)
    at org.yaml.snakeyaml.Yaml.loadFromReader(Yaml.java:505)
    at org.yaml.snakeyaml.Yaml.load(Yaml.java:424)
    at org.embulk.deps.config.YamlProcessorImpl.load(YamlProcessorImpl.java:32)
    at org.embulk.config.ConfigLoader.fromYamlString(ConfigLoader.java:56)
    at org.embulk.EmbulkRunner.readConfig(EmbulkRunner.java:358)
    at org.embulk.EmbulkRunner.run(EmbulkRunner.java:149)
    at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:431)
    at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:90)
    at org.embulk.cli.Main.main(Main.java:64)

Error: while scanning a simple key in 'string', line 61, column 3: mode : truncate_insert ^ could not find expected ':' in 'string', line 62, column 1:

My os version: Oracle Linux 7.9 Java version: openjdk version "1.8.0_392" OpenJDK Runtime Environment (build 1.8.0_392-b08) OpenJDK 64-Bit Server VM (build 25.392-b08, mixed mode)

PostgreSQL Version: 15.4

Listing plugins:Gem plugin path is:

LOCAL GEMS

bundler (1.16.0) did_you_mean (default: 1.0.1) embulk (0.9.25 java) embulk-input-jdbc (0.13.2 java) embulk-input-sqlserver (0.13.2 java) embulk-output-postgresql (0.10.3 java) jar-dependencies (default: 0.3.10) jruby-openssl (0.9.21 java) jruby-readline (1.2.0 java) json (1.8.3 java) liquid (4.0.0) minitest (default: 5.4.1) msgpack (1.1.0 java) net-telnet (default: 0.1.1) power_assert (default: 0.2.3) psych (2.2.4 java) rake (default: 10.4.2) rdoc (default: 4.2.0) test-unit (default: 3.1.1)

And my .yml file. I masked some information,

in: type: sqlserver driver_path: ../Drivers/mssql-jdbc-12.4.2.jre8.jar host: xx.xx.xx.xx port: xx user: xxxx password: "xxxx" database: xxxx query: SELECT RO.PartitionID as PARTITIONID,
RO.MeterDate as OKUMATARIH, RO.CreateDate as KAYITTARIH, RO.MeterID as SAYAC_ID, RO.SubscriberID as ABONE_ID, CAST(0 AS DECIMAL(10,0)) as MODEM_ID, CAST(0 AS DECIMAL(10,0)) as MUSTERI_ID, RO.IndexT as TNUMERIK, ROTR.IndexT1 as T1NUMERIK, ROTR.IndexT2 as T2NUMERIK, ROTR.IndexT3 as T3NUMERIK, ROTR.IndexT4 as T4NUMERIK, RO.VoltageR AS VOLTAJRNUMERIK, ROT.VoltageS AS VOLTAJSNUMERIK, ROT.VoltageT AS VOLTAJTNUMERIK, ROR.RI AS INDUKTIFENERJINUMERIK, ROR.RC AS KAPASITIFENERJINUMERIK, CAST(0.0 AS FLOAT) AS FREKANSRNUMERIK, CAST(0.0 AS FLOAT) AS FREKANSSNUMERIK, CAST(0.0 AS FLOAT) AS FREKANSTNUMERIK, SUB.MeterFactor AS SAYACCARPAN, MET.SerialNumber AS SAYACSERINUMARASI, RO.CurrentR AS AKIMRNUMERIK, ROT.CurrentS AS AKIMSNUMERIK, ROT.CurrentT AS AKIMTNUMERIK, ROT.CosR AS COSRNUMERIK, ROT.CosS AS COSSNUMERIK, ROT.CosT AS COSTNUMERIK, RD.Demand AS DEMANDNUMERIK, RD.DemandDate AS DEMANDTARIHI FROM ReadingReadout RO LEFT JOIN ReadingReadoutReactive ROR ON ROR.PartitionID=RO.PartitionID AND ROR.SubscriberID=RO.SubscriberID AND ROR.MeterID=RO.MeterID LEFT JOIN ReadingReadoutTriphase ROT ON ROT.PartitionID=RO.PartitionID AND ROT.SubscriberID=RO.SubscriberID AND ROT.MeterID=RO.MeterID LEFT JOIN ReadingLPTariff ROTR ON ROTR.PartitionID=RO.PartitionID AND ROTR.SubscriberID=RO.SubscriberID AND ROTR.MeterID=RO.MeterID LEFT JOIN V_Luna_Subscriber SUB ON SUB.ID=RO.SubscriberID AND SUB.MeterID=RO.MeterID LEFT JOIN V_Luna_Meter MET ON MET.ID=RO.MeterID AND MET.SubscriberID=RO.SubscriberID LEFT JOIN ReadingDemand RD ON RD.PartitionID=RO.PartitionID AND RD.SubscriberID=RO.SubscriberID AND RD.MeterID=RO.MeterID WHERE convert(VARCHAR(8),RO.MeterDate,112)=convert(VARCHAR(8),dateadd(day,-1,getdate()),112)

out: type: postgresql driver_path: ../Drivers/postgresql-42.7.0.jar host: xxxx port: xxxx user: xxxx password: "xxxx" database: xxxx schema: xxxx table: xxxx default_timezone: "Europe/Istanbul" mode : truncate_insert

hiroyuki-sato commented 9 months ago

Hello, @KrmBngL

It seems that you are violate YAML style. Try to use | like below. And try to use stdout output for minimizing the probelm.

in:
  type: postgresql
  host: host
  user: user
  database: database
  # write your SQL like below. `|` character and indent are important.
  query: |
    select
      column
    from
      table
    where
      pg_created_at > '2018-01-01'
out:
  type: stdout