HDFS - Casting de données de chaîne dans le fichier csv en bigint dans table de ruche - Stack Overflow

I have a csv file in which the amount field is a string. While loading into the table, I want to convert it into Bigint. I know that I can do cast(column_name as BIGINT) but at which stage should I do that?

I tried creating the table with the data type of amount as Bigint and loaded the file. When I query it, I get null as there is a data type mismatch.

If somebody can show me how to convert it and at what stage, it would be really appreciated.

Thanks!

If csv has amount field as string that means you data will be inside double quotes "12345". So in this case you can't load your data as int/bigint. Because when you run the LOAD command you cannot make any modifications in the data types of the columns.

There are two ways of doing this:

1.) Make your data(amount field) in csv as normal value instead of keeping it as string field.
    Then load your data into the table.

2.) First load your data as string field, then create another table with amount field as int/bigint then do:

insert into new_table select col1,col2..cast(coln as bigint)..colm from old_table

Hope this helps...!!!

Try using a cast to an Unsigned Integer such as

CAST(col_name AS UNSIGNED INTEGER)

I have a csv file in which the amount field is a string. While loading into the table, I want to convert it into Bigint. I know that I can do cast(column_name as BIGINT) but at which stage should I do that?

I tried creating the table with the data type of amount as Bigint and loaded the file. When I query it, I get null as there is a data type mismatch.

If somebody can show me how to convert it and at what stage, it would be really appreciated.

Thanks!

If csv has amount field as string that means you data will be inside double quotes "12345". So in this case you can't load your data as int/bigint. Because when you run the LOAD command you cannot make any modifications in the data types of the columns.

There are two ways of doing this:

1.) Make your data(amount field) in csv as normal value instead of keeping it as string field.
    Then load your data into the table.

2.) First load your data as string field, then create another table with amount field as int/bigint then do:

insert into new_table select col1,col2..cast(coln as bigint)..colm from old_table

Hope this helps...!!!

Try using a cast to an Unsigned Integer such as

CAST(col_name AS UNSIGNED INTEGER)

Source