Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Pass a table to python script and create another table

I have a table in which a column is a php serialized object. I want to de-serialize the column and make another temporary table with the deserialized values.
for example:
the initial table would look like
| col A |
++++++++++++++++
|a:1:{i:145;s:2:"14";} |
|a:1:{i:145;s:2:"15";} |
|a:1:{i:145;s:2:"16";} |

The expected output temporary table for further processing is:
| Col A | Col B |
+++++++++++++++++++++
| 145 | 14 |
| 145 | 15 |
| 145 | 16 |

5 Community Answers

Matillion Agent  

Kalyan Arangam —

hi dilani,

The python component support the Jython interpreter which should allow you easily run any DDL or SQL against Redshift.

The challenge here is to parse the serialised table and derive appropriate DDL.

How is initial table value being passed into python?
Are you aware of any python libraries that let you deserialise into a table structure?

Best
Kalyan


Dilani Maheswaran —

Hi Kalyan,

Initial table values are passed into python from a select query in the script itself.
cursor.execute('select col_a from mytable');

I haven't found any library in python to deserialise data into table structure. I will have to deserialise data and insert into a table through my python script.

Thanks
Dilani


Matillion Agent  

Laura Malins —

Hi Dilani

You can use this library to deserialize the data in Python:
https://pypi.python.org/pypi/phpserialize

However parsing this table in Python will be slow and use a lot of memory. I recommend parsing it in a Transformation Job if possible using one of the Redshift RegEx functions

Thanks
Laura


Dilani Maheswaran —

Hi Laura,

I tried to pip install phpserialize library. But it gives an error. I am unable to install this library. What could be the possible reason?
I tried sudo pip install phpserialize as well.

Thanks
Dilani


Matillion Agent  

Kalyan Arangam —

Hi Dilani,

I managed to install it by using the following command (over ssh) – sudo pip install phpinitialize

You may use the Jython or Python2 interpreter to work with this package.

Best
Kalyan

Post Your Community Answer

To add an answer please login