Mailing-List: contact dev-help@hawq.incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hawq.incubator.apache.org
MIME-Version: 1.0
In-Reply-To: <CAH_aXGOD6hgq2_Rn+REDod-2GBEMK2jy4mQh32fQSCRWJigZqQ@mail.gmail.com>
References: <CAHAuQDxJi9hy2sa4m05ugPuT1Msc34NuSswpoRgkwrptqn-1AA@mail.gmail.com>
 <CAMTypuuF3Nn8AG_KriBessq2Q4Jw1dwzG1x9cXd3Lb97VnMgzQ@mail.gmail.com>
 <CAHAuQDzuD=N=bhg3x0EdNp6t5XMMFa1U=7H+fszNGS7AXAQtTA@mail.gmail.com>
 <CAFht1hKA+qfQbOc--6DdhA9kAo3Zvt0C-B+QxhB3oOeoP0j7_w@mail.gmail.com>
 <CAHAuQDx4y1gPysjxx5HpCCxrGC3F6jLioUPr1XKWkrynt05U1w@mail.gmail.com>
 <CABQrizfdWCU4wNruwM2vR38F50HXjbrRRp-f7N44BaWauqFzzw@mail.gmail.com>
 <CAHAuQDxidVsXeJpwxs9ut0w=xVru7oOnLQLtLD8pQm8hB1DLmA@mail.gmail.com>
 <038744F5-CBEB-41FC-9E5B-2CFEC360498E@me.com> <CAHAuQDwHuu-qfqEN8YT8m7ZM9pGcULT8E=c7+zX_4bBvZi-hNA@mail.gmail.com>
 <CAH_aXGPLbs2Vx+Ci5ic3p9sLtQ5h-+dMUxe_sO_KVAYL_pBEcg@mail.gmail.com>
 <CAMLEP5dYQ4=skxNBpd20z+W1utepuuLwG0qR8=wGJG52f6B3ng@mail.gmail.com>
 <96f0db480b8a4916bd1de784ef5e7ff7@BMPRDEXC142.IGI.IG.LOCAL>
 <895de143c82348318ed349d06962b6a0@BMPRDEXC142.IGI.IG.LOCAL>
 <CAH_aXGO41odj1-RL9WCxXk_QV_P4j1xGF7ARtvm1CxWBcsVHGw@mail.gmail.com>
 <68ce1a339f65492fa0a766b080414e18@BMPRDEXC142.IGI.IG.LOCAL>
 <672AB305-4ADD-428B-936A-AC654C2C9F29@ig.com> <CAH_aXGOD6hgq2_Rn+REDod-2GBEMK2jy4mQh32fQSCRWJigZqQ@mail.gmail.com>
From: Jon Roberts <jroberts@pivotal.io>
Date: Thu, 27 Apr 2017 09:01:19 -0500
Message-ID: <CAH_aXGNZE4bNz7TDbePM5Zuizdzs9RY1hUV3Xm4p7o20r9FTjQ@mail.gmail.com>
Subject: Re: PXF JDBC plugin
To: dev@hawq.incubator.apache.org
Content-Type: multipart/alternative; boundary=94eb2c055f4e3211e3054e2664e9
archived-at: Thu, 27 Apr 2017 14:01:28 -0000

--94eb2c055f4e3211e3054e2664e9
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

https://issues.apache.org/jira/browse/HAWQ-1445

Jon Roberts

On Tue, Apr 25, 2017 at 6:31 AM, Jon Roberts <jroberts@pivotal.io> wrote:

> No I did not.  My post was intended to start a discussion about the plugi=
n
> and I thought the original contributor would chime in.  But I can make th=
e
> tickets now if you think that is the best way to move forward.
>
> Jon Roberts
> Principal Engineer | jroberts@pivotal.io | 615-426-8661 <(615)%20426-8661=
>
>
> On Tue, Apr 25, 2017 at 2:58 AM, Michael Pearce <Michael.Pearce@ig.com>
> wrote:
>
>> Hi Jon,
>>
>> Did you make JIRA issues for the two improvements you suggested to be
>> made?
>>
>> Cheers
>> Mike
>>
>> On 04/04/2017, 04:15, "Michael Pearce" <Michael.Pearce@ig.com> wrote:
>>
>>     Hi Jon
>>
>>     I think on issue 1 and 2 are valid improvements that can be made, ad=
d
>> some Jira's for these. Looking at issue 1 it seems quite trivial for
>> someone to contribute the solution for.
>>
>>     On the question front:
>>
>>     1) from my understanding and using this plugin so far this is what
>> the partion by interval allows you to control the number of partitions t=
hat
>> would be generated, e.g. This is why the sample in document has 1 year s=
o
>> it only creates 2 fragments.  You can also partition by an enum field he=
re
>> the number of partitions is the number of enums.
>>
>>     2) I haven't checked specifically, saying that I haven't run into an
>> issue when trialling this myself. Obviously this is contributed by Devin=
 he
>> will be the definitive source here. Though I guess can always specially
>> test these scenarios.
>>
>>     Sent using OWA for iPhone
>>     ________________________________________
>>     From: Jon Roberts <jroberts@pivotal.io>
>>     Sent: Monday, April 3, 2017 11:57:59 PM
>>     To: dev@hawq.incubator.apache.org
>>     Subject: Re: PXF JDBC plugin
>>
>>     https://github.com/apache/incubator-hawq/tree/master/pxf/pxf-jdbc
>>
>>     Issue 1: Security
>>     The example has the username and password in the connection string.
>>
>>     LOCATION ('pxf://localhost:51200/demodb.myclass'
>>                   '?PROFILE=3DJDBC'
>>                   '&JDBC_DRIVER=3Dcom.mysql.jdbc.Driver'
>>                   '&DB_URL=3Djdbc:mysql://
>>     192.168.200.6:3306/demodb&USER=3Droot&PASS=3Droot'
>>                   )
>>
>>     Any chance we can get this changed to a connection profile that
>> points to a
>>     file outside of the database?  For Greenplum database and S3, the
>> LOCATION
>>     syntax includes "config=3D/path/to/config_file".  The config_file
>> contains
>>     the S3 credentials.  This seems like a good pattern to follow.
>>
>>     As it is right now, anyone that can connect to the database will be
>> able to
>>     see the username and password of the JDBC connection.
>>
>>     Issue 2: Extra Properties
>>
>>     Some JDBC drivers will need many additional properties beyond the UR=
L
>> and
>>     this requires setting it with a put to a Properties variable.  An
>> example
>>     of this is Oracle's defaultRowPrefetch property that needs to be
>> updated
>>     from the default of 10 which is designed for OLTP to something large=
r
>> like
>>     2000 which is more ideal for data extracts.
>>
>>     Additionally, you will need the ability to set the isolation level
>> which is
>>     done with setTransactionIsolation on the Connection.  I don't believ=
e
>> you
>>     can set this on the connection URL either.  Many SQL Server and DB2
>>     database still don't use snapshot isolation and use dirty reads
>> instead.
>>     Without a dirty read here, your query will block modifications to th=
e
>> table
>>     with a "blocking lock".
>>
>>     So for the configuration file will need both an extra properties
>> variable
>>     that is delimited so you can multiple and a isolation level indicato=
r.
>>
>>     Questions.
>>     1.  How do you manage the maximum number of pxf instances?  For
>> example, if
>>     you partition like this:
>>     "PARTITION_BY=3Dcdate:date&RANGE=3D2008-01-01:2010-01-01&INTERVAL=3D=
1:day",
>> will
>>     you create 730 pxf instances and thus 730 concurrent queries to the
>> source
>>     database?
>>     2.  Have you tested special characters like null, escape, carriage
>> return,
>>     newline, and your delimiter?  Maybe that is handled automatically by
>>     pxfwritable_import so this isn't an issue.
>>
>>
>>
>>     Jon Roberts
>>     Principal Engineer | jroberts@pivotal.io | 615-426-8661
>>
>>     On Mon, Apr 3, 2017 at 3:18 PM, Michael Pearce <Michael.Pearce@ig.co=
m
>> >
>>     wrote:
>>
>>     > I mean the readme file :) at that location.
>>     > ________________________________________
>>     > From: Michael Pearce
>>     > Sent: Monday, April 3, 2017 9:15:48 PM
>>     > To: dev@hawq.incubator.apache.org
>>     > Subject: Re: PXF JDBC plugin
>>     >
>>     > On master there is the document that was added.
>>     >
>>     > https://github.com/apache/incubator-hawq/tree/master/pxf/pxf-jdbc
>>     >
>>     > Reading this I think answers some of the questions.
>>     >
>>     >
>>     >
>>     >
>>     >
>>     > Sent using OWA for iPhone
>>     > ________________________________________
>>     > From: Vineet Goel <vvineet@apache.org>
>>     > Sent: Monday, April 3, 2017 6:33:07 PM
>>     > To: dev@hawq.incubator.apache.org
>>     > Subject: Re: PXF JDBC plugin
>>     >
>>     > Devin and others,
>>     >
>>     > It would be great if you can expand a bit more on the
>> implementation or
>>     > point to a document?
>>     >
>>     > Thanks!
>>     >
>>     >
>>     >
>>     > On Mon, Apr 3, 2017 at 7:53 AM Jon Roberts <jroberts@pivotal.io>
>> wrote:
>>     >
>>     > > JDBC PXF is pretty exciting.  Are there details on how this work=
s?
>>     > >
>>     > > Does PXF randomly pick a segment to connect to the JDBC source o=
r
>> do you
>>     > > specify a particular node to execute the query?  I'm assuming it
>> isn't a
>>     > > parallel query so you don't flood the JDBC source with x number =
of
>>     > "select
>>     > > *" queries but I could be wrong.
>>     > >
>>     > > How do you register the JDBC connection string with PXF?
>>     > >
>>     > > How do you manage credentials?
>>     > >
>>     > > How do you register the JDBC jar files?
>>     > >
>>     > >
>>     > > Jon Roberts
>>     > >
>>     > > On Sat, Apr 1, 2017 at 5:05 PM, Ed Espino <espino@apache.org>
>> wrote:
>>     > >
>>     > > > Thank you Michael, this is a well-deserved report highlight. I
>> have
>>     > added
>>     > > > the following to the report:
>>     > > >
>>     > > >   3. Community contribution highlight(s):
>>     > > >
>>     > > >      * Leveraging the extensible PXF design, a JDBC PXF plugin
>>     > > >        was contributed by Devin Jia (github id: jiadexin). Thi=
s
>>     > > >        contribution came from the community and not from the
>>     > > >        company which originally donated HAWQ to the ASF.
>>     > > >
>>     > > > Warm regrds,
>>     > > > -=3De
>>     > > >
>>     > > > On Sat, Apr 1, 2017 at 1:14 PM, Michael Andr=C3=A9 Pearce <
>>     > > > michael.andre.pearce@me.com> wrote:
>>     > > >
>>     > > > >
>>     > > > > I would add note about the new JDBC PXF plugin being
>> contributed by
>>     > > Devin
>>     > > > > Jia. (Kudos to him)
>>     > > > >
>>     > > > > It's a good show of the community contributing and growing
>> outside of
>>     > > > just
>>     > > > > Pivotal staff (I think for incubator review this is
>> important) and
>>     > also
>>     > > > the
>>     > > > > benefit of the extensible PXF design the project has done.
>>     > >
>>     > The information contained in this email is strictly confidential
>> and for
>>     > the use of the addressee only, unless otherwise indicated. If you
>> are not
>>     > the intended recipient, please do not read, copy, use or disclose
>> to others
>>     > this message or any attachment. Please also notify the sender by
>> replying
>>     > to this email or by telephone (+44(020 7896 0011) and then delete
>> the email
>>     > and any copies of it. Opinions, conclusion (etc) that do not relat=
e
>> to the
>>     > official business of this company shall be understood as neither
>> given nor
>>     > endorsed by it. IG is a trading name of IG Markets Limited (a
>> company
>>     > registered in England and Wales, company number 04008957) and IG
>> Index
>>     > Limited (a company registered in England and Wales, company number
>>     > 01190902). Registered address at Cannon Bridge House, 25 Dowgate
>> Hill,
>>     > London EC4R 2YA. Both IG Markets Limited (register number 195355)
>> and IG
>>     > Index Limited (register number 114059) are authorised and regulate=
d
>> by the
>>     > Financial Conduct Authority.
>>     >
>>
>>
>> The information contained in this email is strictly confidential and for
>> the use of the addressee only, unless otherwise indicated. If you are no=
t
>> the intended recipient, please do not read, copy, use or disclose to oth=
ers
>> this message or any attachment. Please also notify the sender by replyin=
g
>> to this email or by telephone (+44(020 7896 0011) and then delete the em=
ail
>> and any copies of it. Opinions, conclusion (etc) that do not relate to t=
he
>> official business of this company shall be understood as neither given n=
or
>> endorsed by it. IG is a trading name of IG Markets Limited (a company
>> registered in England and Wales, company number 04008957) and IG Index
>> Limited (a company registered in England and Wales, company number
>> 01190902). Registered address at Cannon Bridge House, 25 Dowgate Hill,
>> London EC4R 2YA. Both IG Markets Limited (register number 195355) and IG
>> Index Limited (register number 114059) are authorised and regulated by t=
he
>> Financial Conduct Authority.
>>
>
>

--94eb2c055f4e3211e3054e2664e9--