Invalid value when using insert into

Toad Data Point
1

Hi,
I was trying to insert values into a new table by using

insert into new_table (col1, col2...)
Select col1, col2..
from old_table

One of the columns has key: string pair values that look something like below:
col2
data: "{\n \ "abc" : [\n {\n "efg"]}"

So when I tried to insert values from col1 along with values from other columns. It messes up values in the other columns. For example:
original values in col1 look like:
col1
1
2
3

when inserted into a new table along with col2, the resultant table would have records like:
col1 col2
{ null
null null
null null
null null
1 null
2 null
3 null
{ null

Can anyone help me resolve this issue? Thanks!

2

-- represents space
below is what the sample table would look like after I tried to insert values from col1 and col2
col1--------col2
{ --------- null
null --------null
null --------null
null --------null
1 ------------null
2 ------------null
3 ------------null
{ -------------null

3

Would help to understand what database/source you're trying to get this to work on, what version of TDP, and then the actual SQL, and a looksie at a few rows in the source table, and then what you expect a few rows of the target table to look like.

Having said that, I can tell you that care needs to be taken when working with data that includes escape codes or control chars. e.g. a database parser's interpreter might evaluate /n as a "return" or "newline". Also, careful with two single quotes (e.g. representing NULL, in most cases) vs. double-quotes (beginning of constant string, etc.) .... I suspect some of that is what's happening here, without knowing more details.

4

Hi Gary, I am using TDP 4.3 and hive. I am expecting to get an output that would look something like:

col1 col2
1 data: "{\n \ "abc" : [\n {\n "efg"]}"
2 data: "{\n \ "abc" : [\n {\n "efg"]}"
3 data: "{\n \ "abc" : [\n {\n "efg"]}"

The source table only has one column and each record in the column is a json data that has many layers and col2 contains the data from one of the layers. I was suspecting the same that the escape codes were causing this issue. But, I don't know how to remove those \n in

data:"{\n \ "abc" : [\n {\n "efg"]}"
If I want to insert this type of data into col2 and keep its original format which is like
data:"{\n \ "abc" : [\n {\n "efg"]}"
How would I do that?

5

Ah... if it's Hive (one of TDP's read-only data sources, per Help doc below) then you'll need to find another way to create your table...e. g. command line or native tool?

If your target table does not need to be a Hadoop table, you can create your target in Local Storage, or on a relational database that you have connection and object creation/change privs to...

image
image702×353 18.8 KB

7

Thanks for your reply Gary, and it is a hive table. Do you know if there's a way change \n in my data to \n directly in TDP?

8

Going to have to use some other utility to make the data changes... if it helps, TDP's Automation script can allow such a utility to be called within a TDP workflow that you define (e.g. first step: export source table rows to some (flat?) file, and then second step: run the utility on your workstation that takes flat file data, connects to Hadoop via Hive and then updates target table.)
But this presumes you have some utility or app that can perform the changes.