0

I am using AWS DMS to track changes on MySQL DB and migrate data to AWS S3. One of the tables has "Point" type column and DMS stores it as BLOB(example - 00000000010100000085EB51B81E7D5DC044696FF085F14040). How can I transform it back to some geospatial type or readable text?

datahack
  • 477
  • 1
  • 11
  • 32
  • `SELECT ST_AsText(geometry_column)`. https://dev.mysql.com/doc/refman/8.0/en/gis-format-conversion-functions.html#function_st-astext and https://docs.aws.amazon.com/redshift/latest/dg/ST_AsText-function.html – Akina Jun 03 '22 at 09:03
  • Unfortunately, it is not working, I've tried it both on MySQL and Snowflake. – datahack Jun 03 '22 at 09:29
  • You did something wrongly. [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c637e5157113b1369f1e98e6e5c9f1c0) – Akina Jun 03 '22 at 09:35
  • AWS DMS replicates inserts to S3, and when you open csv file in location column you find values like these "00000000010100000085EB51B81E7D5DC044696FF085F14040". That is how DMS handles POINT type from MySQL. – datahack Jun 03 '22 at 09:40
  • Is this binary value stored in S3? if so then use 2nd link provided above. Or, if you may use custom query for retrieving the data from MySQL then query not `SELECT column FROM table` but `SELECT ST_AsText(column) AS column FROM table` (if it is possible then you may create a view) - and S3 will receive WKT, not POINT. – Akina Jun 03 '22 at 09:43
  • @Akina thank you for your help. It looks like DMS gives back hexadecimal value of MySQL POINT type. That is why I can insert this hexadecimal value into MySQL column of that POINT type. But I am trying to export data from MySQL to S3 to use it with Apache Spark. And I do not have options at this moment to create view to extract mi lat/long values as separate integer columns or something similar. – datahack Jun 03 '22 at 12:13
  • DMS can not transform(apply any geospatial functions) on MySQL point column, it only takes it and stores it on the destination as hexadecimal value. That hexadec value can only be inserted into MySQL compatible DB because the value behind it is not WKT, it MySQL point. So I have to find a way to bypass this... – datahack Jun 03 '22 at 16:05
  • Use the function by provided link, convert and resave. – Akina Jun 03 '22 at 18:08
  • I can’t use it, dms reads binlog and stores location point as blob. – datahack Jun 03 '22 at 18:20

0 Answers0