Error importing data in postgres


#1

I am using snowplow clojure collector, and snowplow javascript to send events from the website.
While processing using r87, the loader task is failing with the error as follows.

Unexpected error: Java::OrgPostgresqlUtil::PSQLException error loading /home/ec2-user/postgres/run=2017-04-17-17-48-34/atomic-events/part-00000: ERROR: invalid input syntax for integer: "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36"
  Where: COPY events, line 1, column pp_yoffset_min: "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2..."
uri:classloader:/storage-loader/lib/snowplow-storage-loader/postgres_loader.rb:50:in `load_events'
uri:classloader:/storage-loader/bin/snowplow-storage-loader:56:in `block in (root)'
uri:classloader:/storage-loader/bin/snowplow-storage-loader:51:in `<main>'
org/jruby/RubyKernel.java:973:in `load'
uri:classloader:/META-INF/main.rb:1:in `<main>'
org/jruby/RubyKernel.java:955:in `require'
uri:classloader:/META-INF/main.rb:1:in `(root)'
uri:classloader:/META-INF/jruby.home/lib/ruby/stdlib/rubygems/core_ext/kernel_require.rb:1:in `<main>'

Here is the data in the file

3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-17 10:26:23.000 2017-04-17 10:26:07.818 page_ping       006db377-544f-4e19-94d4-bff0c0a279fc            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.124.37  126427294       3fa8ac9857503da9        15      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      07      Delhi           28.666702       77.216705       Delhi                              https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages      Smugglr https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard    https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages                   https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard                                                                         0
        0       23      81      Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1600    825                             Asia/Kolkata                    1440    900     UTF-8   1600    18603                                                           Asia/Kolkata
                                                        827e1835-8e4a-4aa7-aa08-0cb4d7910d5c    2017-04-17 10:26:23.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-12 13:39:20.000 2017-04-12 13:36:33.598 page_ping       00b915a3-a396-4567-8442-5ba8495d2bc6            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          124.40.244.24   3418863117      f5c12ce7f1d25670        12      14826713-cc24-4209-8c1c-b70bd897fa76    IN      19      Bangalore       560076  12.983307       77.58331        Karnataka                  https://smugglr.co/      Pages           https   smugglr.co      80      /                                                                                                                                                                  0
        0       0       0       Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-GB   1       1       0       0  0
        0       0       0       0       1       24      1920    956                             Asia/Kolkata                    1920    1080    UTF-8   1905    2130                                                            Asia/Kolkata
                                                        95f25b8e-9fe8-47a3-a255-6f0d27fb66cb    2017-04-12 13:39:20.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-13 08:50:36.000 2017-04-13 08:50:31.469 page_ping       0271caa5-6a52-4a39-80f1-e1e6f8c6f791            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.23.235  126427294       3fa8ac9857503da9        8       d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      36      Ghaziabad       201001  28.666702       77.43329        Uttar Pradesh              https://smugglr.co/      Pages           https   smugglr.co      80      /                                                                                                                                                                  0
        0       0       62      Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1600    825                             Asia/Kolkata                    1440    900     UTF-8   1600    2012                                                            Asia/Kolkata
                                                        447ab4d8-8762-42bc-a891-de9756ca3304    2017-04-13 08:50:36.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
snowplow-backend        srv     2017-04-17 17:48:34.603 2017-04-12 11:00:48.000 2017-04-12 11:00:48.851 unstruct        02f36cf7-bf08-4517-8570-607936cdf1b4                    rb-0.5.2        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          52.40.154.102                           10f15b5d-a5b0-4b40-aebb-3f03a09ac562    US      OR      Boardman        97818   45.8696 -119.688        Oregon                                                                     Ruby                                                                                                                                                                                                                                        America/Los_Angeles                                                              2017-04-12 11:00:48.000 com.snowplowanalytics.monitoring.batch  emr_job_failed  jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-13 13:49:12.000 2017-04-13 13:49:02.156 page_ping       03326eba-7d22-4586-a7a9-e397e6ce25bf            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.23.235  1500654254      d5a8cec579ebc2e6        1       023d0d16-8318-4afa-b3cb-85660d9857a7    IN      36      Ghaziabad       201001  28.666702       77.43329        Uttar Pradesh              https://staging.smugglr.co/      Smugglr | Build chat bots for Facebook messanger                https   staging.smugglr.co      80      /                                                                                                  0
        0       0       0       Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:52.0) Gecko/20100101 Firefox/52.0                                              en-US   0       1       0       0       0       0       0       0       0  1
        24      1440    737                             Asia/Kolkata                    1440    900     UTF-8   1440    4645                                                            Asia/Kolkata                                       274b0f7f-13b7-48a1-9838-64334a487077     2017-04-13 13:49:12.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-15 10:26:26.000 2017-04-15 10:26:16.289 page_ping       03b12da6-7072-48d5-a01c-b5c216dacd54            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          171.79.72.128   126427294       3fa8ac9857503da9        13      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      07      Delhi           28.666702       77.216705       Delhi                              https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages      Smugglr https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard    https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages                   https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard                                                                         0
        0       0       0       Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1611    825                             Asia/Kolkata                    1440    900     UTF-8   1611    18055                                                           Asia/Kolkata
                                                        41cb1cb5-4901-4828-9b45-9ca83accfcda    2017-04-15 10:26:26.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-14 18:16:33.000 2017-04-14 18:16:17.302 page_ping       03b8f6dc-4464-44d7-88fe-f25e999ffa50            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          103.212.147.207 2481309680      2d5c9775d17bc5a0        1       eefe19e1-2181-4ef5-a227-7a05f93bff51    IN      07      Delhi   110045  28.666702       77.216705       Delhi                              https://smugglr.co/      Smugglr | Build chat bots for Facebook messanger        https://l.facebook.com/ https   smugglr.co      80      /                       https   l.facebook.com  80      /                                          0
        0       0       0       Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0                                               en-US                                                                           1  24       1366    659                             Asia/Kolkata                    1366    768     UTF-8   1349    4654                                                            Asia/Kolkata                                               3f06df13-81c2-4272-ae1d-225e58995b2f     2017-04-14 18:16:33.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-15 10:28:26.000 2017-04-15 10:28:21.286 page_ping       04896794-08f7-4fb8-ae8e-fdb6b6b9efb8            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          171.79.72.128   126427294       3fa8ac9857503da9        13      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      07      Delhi           28.666702       77.216705       Delhi                              https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages      Smugglr https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard    https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages                   https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard                                                                         0
        0       0       0       Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1611    825                             Asia/Kolkata                    1440    900     UTF-8   1611    18055                                                           Asia/Kolkata
                                                        41cb1cb5-4901-4828-9b45-9ca83accfcda    2017-04-15 10:28:26.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-14 07:50:49.000 2017-04-14 07:50:41.321 page_ping       04d3310f-165d-4c76-beb0-cdee33c6a651            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          182.71.154.42   4264970948      b8915630b7532b0a        1       621238c1-3501-4c0c-a1a2-424e042f87ce    IN                              20.0    77.0                                            https://smugglr.co/ Smugglr | Build chat bots for Facebook messanger                https   smugglr.co      80      /                                                                                                                                  0
        0       0       0       Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0                                               en-US   0       1       0       0       0       0       0       0       0       1  24       1366    659                             Asia/Kolkata                    1366    768     UTF-8   1349    4645                                                            Asia/Kolkata                                               6284bc7e-d6a4-45d8-9137-3d1b05c2eb9c     2017-04-14 07:50:49.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-14 06:53:57.000 2017-04-14 06:35:35.717 page_view       a64c526b-ced4-482c-a7e2-e2a6b806c429            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.18.155  126427294       3fa8ac9857503da9        11      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      36      Ghaziabad       201001  28.666702       77.43329        Uttar Pradesh              https://smugglr.co/      Pages           https   smugglr.co      80      /                                                                                                                                                                  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                                en-US   1       1       0       0       0       0       0       0  0
        1       24      1611    825                             Asia/Kolkata                    1440    900     UTF-8   1611    2012                                                            Asia/Kolkata                               02c1dc35-510c-45a5-bb3b-40f15dd05721     2017-04-14 06:53:57.000 com.snowplowanalytics.snowplow  page_view       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-14 06:53:59.000 2017-04-14 06:35:35.717 page_view       c0494825-86f3-42a5-b927-a9ed86d7a7a4            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.18.155  126427294       3fa8ac9857503da9        11      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      36      Ghaziabad       201001  28.666702       77.43329        Uttar Pradesh              https://smugglr.co/      Pages           https   smugglr.co      80      /                                                                                                                                                                  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                                en-US   1       1       0       0       0       0       0       0  0
        1       24      1611    825                             Asia/Kolkata                    1440    900     UTF-8   1611    2012                                                            Asia/Kolkata                               02c1dc35-510c-45a5-bb3b-40f15dd05721     2017-04-14 06:53:59.000 com.snowplowanalytics.snowplow  page_view       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-13 08:50:29.000 2017-04-13 06:11:08.621 page_ping       066829b7-7d78-4b34-bf62-cc42cc9e4118            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.23.235  126427294       3fa8ac9857503da9        7       d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      36      Ghaziabad       201001  28.666702       77.43329        Uttar Pradesh              https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard     The Quint | Dashboard   https://smugglr.co/     https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard https    smugglr.co      80      /                                                                                                                                                                                                          0
        0       177     177     Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1600    825                             Asia/Kolkata                    1440    900     UTF-8   1600    4227                                                            Asia/Kolkata
                                                        40db1e6d-3acc-4706-a586-b1906747e262    2017-04-13 08:50:29.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-15 10:27:31.000 2017-04-15 10:27:31.287 page_ping       06d28f46-d6b5-461b-a335-7f4d3f300507            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          171.79.72.128   126427294       3fa8ac9857503da9        13      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      07      Delhi           28.666702       77.216705       Delhi                              https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages      Smugglr https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard    https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages                   https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard                                                                         0
        0       0       67      Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1611    825                             Asia/Kolkata                    1440    900     UTF-8   1611    18055                                                           Asia/Kolkata
                                                        41cb1cb5-4901-4828-9b45-9ca83accfcda    2017-04-15 10:27:31.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-17 03:28:23.000 2017-04-17 03:27:22.330 page_view       06ee1583-d118-43d8-a89e-c114677085df            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          43.242.118.1    3923736197      dd90337a95f6c9ee        1       09cde265-f1b3-4d48-9301-3bd9bdebc089    IN                              20.0    77.0                                            https://smugglr.co/ Smugglr | Build chat bots for Facebook messanger        http://m.facebook.com/  https   smugglr.co      80      /                       http    m.facebook.com  80      /                                                          Mozilla/5.0 (Linux; Android 6.0.1; Redmi 3S Build/MMB29M; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/56.0.2924.87 Mobile Safari/537.36 [FB_IAB/MESSENGER;FBAV/106.0.0.23.70;]                                         en-US                                                                               1       32      360     568                             Asia/Kolkata                    360     640     UTF-8   360     568                                Asia/Kolkata                                                     0b26a076-f6e0-4be3-9217-532edd17d155    2017-04-17 03:28:23.000 com.snowplowanalytics.snowplow  page_view       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-17 10:26:48.000 2017-04-17 10:26:42.816 page_ping       07af875b-06ab-4458-ae6c-91e8b981d4fb            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.124.37  126427294       3fa8ac9857503da9        15      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      07      Delhi           28.666702       77.216705       Delhi                              https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages      Smugglr https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard    https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages                   https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard                                                                         0
        0       0       213     Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1600    825                             Asia/Kolkata                    1440    900     UTF-8   1600    18603                                                           Asia/Kolkata
                                                        827e1835-8e4a-4aa7-aa08-0cb4d7910d5c    2017-04-17 10:26:48.000 com.snowplowanalytics.snowplow  page_ping       jsonschema      1-0-0
3e2f2964-6c7c-4576-9b54-e48c7d3f5486    web     2017-04-17 17:48:34.603 2017-04-17 10:26:48.000 2017-04-17 10:26:47.820 page_ping       09328604-77c0-4fe5-b51e-7d600370c50d            cf      js-2.5.1        clj-1.1.0-tom-0.2.0     hadoop-1.8.0-common-0.24.0          223.190.124.37  126427294       3fa8ac9857503da9        15      d5dbc1c6-5278-46a9-a623-c2165b5405ae    IN      07      Delhi           28.666702       77.216705       Delhi                              https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages      Smugglr https://smugglr.co/dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard    https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/messages                   https   smugglr.co      80      /dashboard/fb_pages/494d9751-12c6-47aa-97f0-d9f268e8f1c8/dashboard                                                                         0
        0       0       867     Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36                                               en-US   1       1       0       0  0
        0       0       0       0       1       24      1600    825                             Asia/Kolkata                    1440    900     UTF-8   1600    18603                                                           Asia/Kolkata