phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ken Hampson <>
Subject Re: Importing Postgres-generated CSVs into Phoenix
Date Sat, 09 Jan 2016 03:22:29 GMT
Sure, no problem. I've added a comment indicating my interest to SQOOP-2649.

- Ken

On Fri, Jan 8, 2016 at 8:33 PM James Taylor <> wrote:

> Sounds good, Ken. If you could comment on SQOOP-2649 that you'd be
> interested in using that functionality if it were available, that'd be much
> appreciated.
> Thanks,
> James
> On Fri, Jan 8, 2016 at 5:04 PM, Ken Hampson <> wrote:
>> Hi, James:
>> Thanks for the reply. I took a look at the patch attached to
>> PHOENIX-2434, and it does look like it has a good chance to solve the
>> problem, as Postgres exports boolean values as 't' and 'f', which the patch
>> now checks for. Once we're able to get to Phoenix 4.7.0, I'll definitely
>> try it out to confirm.
>> We don't and won't be able to use Flyway due to various architectural
>> considerations, but we do use Sqoop to get data from Postgres into Hive. I
>> had skimmed  SQOOP-2649 when originally looking at how to bulk load into
>> Phoenix, and figured CSV was the way to go for now since the  SQOOP-2649
>> enhancement was still in patch form. It's definitely something I will keep
>> an eye on going forward.
>> Thanks again,
>> - Ken
>> On Fri, Jan 8, 2016 at 1:13 PM James Taylor <>
>> wrote:
>>> Hi Ken,
>>> PHOENIX-2434 improved our CSV handling of booleans and will appear in
>>> our upcoming 4.7.0 release. It'd be good if you can confirm whether or not
>>> this is what you need. We definitely want to support ingest of CSVs from
>>> other RDBMSs.
>>> There are a couple of other avenues of ingest into Phoenix:
>>> - using Flyway DB ( where Phoenix support has been
>>> added (not sure if this has appeared in a release yet or not, though)
>>> - through a pull request to add support for Phoenix in Scoop (
>>> Unfortunately, this isn't getting much
>>> attention in that community (if this is useful for you, you may want to
>>> chime in on SQOOP-2649 or on their mailing list).
>>> Thanks,
>>> James
>>> On Thu, Jan 7, 2016 at 5:28 PM, Ken Hampson <> wrote:
>>>> Hi, all:
>>>> I am in the process of importing some data from Postgres into Phoenix
>>>> as part of a larger project. I noticed after doing so and cross-checking
>>>> the results of a fairly complex JOIN query (of 6 tables of so) that the
>>>> number of rows returned by Postgres and Phoenix differed -- Phoenix had a
>>>> significant number more rows. I investigated this further, and ultimately
>>>> determined that the reason was that all *boolean *values were imported
>>>> as being false, regardless of their actual value.
>>>> When I investigated that aspect further, I discovered that Phoenix
>>>> appears to require the full strings "true" and "false" in the CSV in order
>>>> to properly register their actual values. As a workaround, I am currently
>>>> using a full SELECT in the "\copy" statement in psql to select each column
>>>> individually, and for each boolean column, I am using a CASE statement to
>>>> emit the full strings. I have since also learned that casting the boolean
>>>> to the "text" datatype will also cause Postgres to emit the full string.
>>>> In either case, though, the pain point is that this discrepancy
>>>> requires that I examine each table closely for boolean columns, and if
>>>> there are any, I then need to enumerate all the columns out explicitly and
>>>> use either the CASE statement or the cast method on the boolean columns in
>>>> order to get the values Phoenix expects. In my case, I didn't need to
>>>> transform any of the data otherwise, so this forced a much more complex
>>>> procedure.
>>>> While psql provides many options to change various values -- delimiters
>>>> and what not -- there is no option to change the boolean values. Likewise,
>>>> as near as I can tell, there are no options to change what Phoenix expects,
>>>> either.
>>>> I am wondering:
>>>> - Is there any way from within Phoenix to change the boolean values
>>>> that it expects in the CSV?
>>>> - Are there any other methods from a Phoenix perspective that you can
>>>> think of that would obviate the need for the column-by-column enumeration
>>>> procedure I outlined above?
>>>> - Since Postgres is a major open source RDBMS which I suspect many
>>>> people -- myself included -- will want to export data from into Phoenix,
>>>> could there be an enhancement to Phoenix to allow an option to specify a
>>>> default boolean value, or perhaps an option to specify the CSV source, from
>>>> which the proper value could be defined and utilized automatically?
>>>> Thanks,
>>>> - Ken

View raw message