Skip to content

Add casting from arbitrary arrow types #5016

Closed

Description

@alamb

Is your feature request related to a problem or challenge? Please describe what you are trying to do.
While porting some tests to sqllogictest I want t ensure the same coverage for specific types.

For example

https://github.com/apache/arrow-datafusion/blob/350cb47289a76e579b221fe374e4cf09db332569/datafusion/core/tests/sql/mod.rs#L1233

It is easy to cast to some SQL datatype:

-- casts `x` to `bigint` which maps to DataType::Int64
select x::bigint from foo;
-- casts `x` to timestamp (DatType::Timestamp)
select x::timestamp from foo;

However, it is not possible to use SQL to name certain arrow types (e.g TimestampMicrosecondasTIMESTAMP` maps to TimestampNanos

I would like a way to convert an expression to an arbitrary arrow type.

Describe the solution you'd like
I would love to do something like

arrow_cast(source, target_type)

Where target_type is a string that describes an arrow tyoe

For example:

--  casts x to a Int8 (which I don't think is possible in sql)
select arrow_cast(x, 'Int8') from foo;
--  casts x to a LargeBinary
select arrow_cast(x, 'LargeBinary') from foo;

I would like the values accepted as 'target_datatype' to t same as returned by the arrow_typeof() function that goes the other direction (expression to string that represents Arrow type)

❯ select arrow_typeof('5');
+------------------------+
| arrowtypeof(Utf8("5")) |
+------------------------+
| Utf8                   |
+------------------------+
1 row in set. Query took 0.028 seconds.
❯ select arrow_typeof(5);
+-----------------------+
| arrowtypeof(Int64(5)) |
+-----------------------+
| Int64                 |
+-----------------------+

Describe alternatives you've considered

It would be nice to have some way to specify Arrow types in SQL datatype syntax. Perhaps like CAST X as CUSTOM TYPE 'Int64'. This would allow using functions such astry_cast, as well as creating tables with specific column types.

However, I am not really sure how to do this in the parser

Additional context
See #4460 for more details

Activity

  1. alamb commented on Jan 21, 2023

    @alamb
    ContributorAuthor

    I think this is apretty good first issue as it is well specified and you can follow the model of arrow_typeof to add a new function and there is an arrow cast kernel that does all the data type conversions

  2. comphead commented on Jan 23, 2023

    @comphead
    Contributor

    @alamb I'd like taking this

  3. alamb commented on Jan 23, 2023

    @alamb
    ContributorAuthor

    Thanks @comphead !

  4. melgenek commented on Jan 24, 2023

    @melgenek
    Contributor

    It would be also interesting to support "custom" types in CREATE TABLE statements. This would help to migrate tests like this one https://github.com/apache/arrow-datafusion/blob/master/datafusion/core/tests/sql/aggregates.rs#L913-L976 to sqllogictest.

  5. alamb commented on Jan 24, 2023

    @alamb
    ContributorAuthor

    It would be also interesting to support "custom" types in CREATE TABLE statements. This would help to migrate tests like this one https://github.com/apache/arrow-datafusion/blob/master/datafusion/core/tests/sql/aggregates.rs#L913-L976 to sqllogictest.

    I was thinking we could use CREATE TABLE ... AS SELECT ... which creates columns based on the types of the SELECT query

    So I think we could make a int8 column in a table like

    CREATE TABLE foo as select arrow_cast(c', 'Int8') as c_int8 from foo
    
  6. comphead commented on Jan 31, 2023

    @comphead
    Contributor

    @alamb in parser we operate with expressions which works with arrow types.
    I understand the necessity of having arrow_typeof which seems to be a synonym for PG pg_typeof function.

    But for the potential arrow_cast function, we will return Expression as well with Arrow datatype inside, how it differs from usual cast? Perhaps you have the analogue in PG? I went through https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast/ but didn't find anything similar

  7. alamb commented on Jan 31, 2023

    @alamb
    ContributorAuthor

    But for the potential arrow_cast function, we will return Expression as well with Arrow datatype inside, how it differs from usual cast?

    It differs because normal casting CAST(x as target_type) or x::target_type can only cast to SQL types (not Arrow types). DataFusion has a mapping from SQL Type --> Arrow type but there are Arrow types without any corresponding SQL type.

    Perhaps you have the analogue in PG? I went through https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast/ but didn't find anything similar

    I don't think there is any analog in postgres; The difference is that you can refer to all types in postgres directly using SQL. Since posgres types are sql types there is no analog to this ticket's proposal in SQL

  8. comphead commented on Jan 31, 2023

    @comphead
    Contributor

    @alamb please correct me if I'm wrong so

    select arrow_cast(x, 'Int8') from foo;
    

    will have the same output as

    select x::tinyint from foo;
    
  9. alamb commented on Jan 31, 2023

    @alamb
    ContributorAuthor

    @alamb please correct me if I'm wrong so

    @comphead you are correct. Here is an example I don't think you can write with SQL:

    select arrow_cast(x, 'TimestampMillisecond')

    because

    select x::timestamp

    defaults to TimestampNanosecond

  10. alamb commented on Feb 2, 2023

    @alamb
    ContributorAuthor

    @comphead I wonder if you plan to work on this issue soon? It would be very helpful for writing sqllogictests for dictionary arrays (e.g. #5151). I may take a shot at writing it up this weekend if you haven't had a chance

  11. comphead commented on Feb 2, 2023

    @comphead
    Contributor

    @alamb sorry, haven't started it yet. made couple of drafts, but thats it. If this is a priority thing, please feel free to take this on

  12. alamb commented on Feb 2, 2023

    @alamb
    ContributorAuthor

    @alamb sorry, haven't started it yet. made couple of drafts, but thats it. If this is a priority thing, please feel free to take this on

    Thanks @comphead -- I will post on this ticket if/when I start work

  13. alamb commented on Feb 2, 2023

    @alamb
    ContributorAuthor

    I am starting to hack on this

  14. assigned and unassigned on Mar 3, 2023
  15. alamb commented on Mar 4, 2023

    @alamb
    ContributorAuthor

    #5166 is now ready for review (I really want this function so I can more easily write / port tests and reproduce bugs)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions