Which databases?

Forgot to add platforms:

Target is MySQL.  The source was Oracle in this example.

I don't think we can support this as requested. Each blob value equates to a separate file. If exporting multiple rows, this equates to generating multiple files. IMO import/export is not the right place to handle blobs.

For this issue, we should probably add an option to exclude blob columns during export, and display a warning (ie "importing into a blob column will not work as intended") when importing into a blob column.

Hi Devin,

Can you try doing the select on the blob column in mysql after you shut off the convert binary to hex in options? See here.

Thanks,

Tom

ADS currently exports BLOB columns based on the "File > Options > Results > Convert binary to hex" setting. However the import does not take this setting into account. Thus the IMPORT does NOT import exactly what is exported for BLOB column when this setting is turn ON. For example the BLOB values is exported as a HEX string, but is imported as a literal raw value.

For this issue, we need to fix the IMPORT so that it considers the "File > Options > Results > Convert binary to hex" setting. If this setting is OFF, then it should continue the current behavior. If this setting is ON, then ADS needs to assume that the blob values in the import file is hex encoded, and import the BLOB values as HEX strings using the correct format for each supported database type. Note that IMPORT is implemented as a bunch of INSERT statements. Different database types may have different formats for inserting HEX strings. For example in DB2 LUW, the insert statement looks like this: INSERT INTO some_table(..., blob_column) VALUES(..., blob(x'some_hex_string'))

This issue will involve researching the correct format for each supported database. The import will have to work correctly for each supported database.

Note that BLOB values can potentially be very large. In this issue, we need to gracefully handle the low memory or out of memory condition. For example, during such condition, ADS should NOT hang or crash, but should display an error dialog with the message:

There is insufficient memory to perform this operation. Please increase the amount of memory available to the application and the JVM according to the application documentation and restart.

This error dialog and message already exist in ADS, and the condition is already handled. This handling needs to be validated due to the nature of BLOB values being large and such condition is more like to occur when importing BLOB values.

Attached Import-Blob.patch contains the fix for a few databases (Oracle, DB2, etc.). Will need to test and fix for all supported databases.

Revision no. 57503
Author: ajit.kulkarni

<aquadatastudio:#15687> Improve Export and Import for BLOBs cross databases

Changes:

1. Added support import blob datatype while import file for some databases and

will continue with remaining database those are supports blob datatype or syntax

@nhi Please find below list of database, those are fixed, doesn't support blob or insert query with blob datatype databases and working on other databases.

Amazon Redshift(Doesn't support BLOB datatype)

Apache Cassandra

Apachy Derby(Insert query doesn’t support BLOB datatype)

Hive(Doesn't support BLOB datatype)

DB2

Greenplum(Doesn't support BLOB datatype)

Informix(Insert query doesn’t support BLOB datatype)

Maria

Mysql

MS SQL Server(!Doesn't support BLOB datatype)

Oracle

@Dev,

For MS SQL Server, we can insert BLOB data using insert statements. Here is sample program. And the blob data format looks like this:

String s = "0xC9CBBBCCCEB9C8CABCCCCEB9C9CBBB"; //SQL Server format

Table DML:

CREATE TABLE `dbo`.`test_blob`  (
      `name`     varchar(25) NULL,
      `baddress` varbinary(2000) NULL
      )
      ON `PRIMARY`
      WITH (
      DATA_COMPRESSION = NONE
      )
      GO

@Asif,

As mention in the ticket, we are considering only BLOB datatype for Import and Export and fixing for BLOB datatype.

Let us know your view point on that.

Whatever changes are made to the ImportThread and ExportThread classes should also be made to the CoreLibImportThread and CoreLibExportThread classes. CoreLibImportThread/CoreLibExportThread  is used to support Fluidshell SQLImport/SQLExport. These should also be tested with the same test cases as ImportThread/ExportThread.

There is no corresponding class in Fludishell for ImportExeclImportThread as this is not supported.

@Tom, @Nhi Please find below list of databases.

@Ajit, please recheck the BLOB support for each database type where you currently indicate that it is not supported. Some database supports it, but it may use a different name for the data type. Basically when we say BLOB in this issue, we meant binary data type (BLOB stands for binary large object). For example MS SQLServer calls it BINARY or VARBINARY; they mean the same as BLOB. Please see: https://www.developer.com/net/asp/article.php/3761486/Working-with-Binary-Large-Objects-BLOBs-Using-SQL-Server-and-ADONET.htm

As mention in the ticket, we are considering only BLOB datatype for Import and Export and fixing for BLOB datatype.

@Ajit, please update the table you have listed here to include the actual blob data type name. As Nhi pointed out we might have missed some blob datatypes. 

As Tom, we will have implications in Fluidshell sqlexport and sqlimport commands for blob support. 

Thanks

@All Please find below list of databases, those are supports BLOB datatype.

@Tom, @Asif I have added support for all databases, those are supports BLOB/Binary datatype except Informix. I have gone through their documentation but could not able to get helpful information. It seems that Informix databases doesn't supports insert query. And for now I added null value where insert doesn't supports.

Would you let me know, if you finds any helpful information.

@Ajit, the original patch already handles Informix. Using latest from SVN, I've just tested import of blob data on Informix database (Informix 172.24.1.140 v11.70), and seems to work. It's imported to the "t2" table in the "tom" database.

@Nhi, In current implementation, we are setting NULL while importing file, if database does not support insert query with BLOB(Informix). Its generates query as INSERT INTO informix.import1(c1) VALUES(NULL).

I have reverted current implementation and added patch code but it gets failed while importing file and gives syntax error. Its generates query as INSERT INTO informix.import(c1) VALUES(x'd0cf11e0a1b11ae....')

I don't think that you can directly insert hex data into blob/clob/byte columns in Informix??

My test was based on Transaction Type = Batch, which is configured in the last page of the Import wizard. In this mode, the import uses prepared statements.

@Documentation: We need to document that importing BLOBs in Informix must use Transaction Type = Batch in order for the import to work correctly.

@Ajit: Please make sure that you test your code changes with all available Transaction Type in the Import wizard.

Code changes look good.

So far, the import of XLSX file into Informix database does not work correctly. Please see the attached export-blob.xlsx file.

1. Check the "File > Options > Results > Convert binary to hex" option
2. Import the export-blob.xlsx file to the tom.informix.t2 table in the Informix 172.24.1.140 v11.70 database
3. In the last Import wizard page, set the "Transaction Type" to Batch, and complete the import
4. Result: The hex encoded binary data in the XLSX file is not imported correctly to the database

Note that if you import the attached export-blob.csv file to the same database table, the data will be imported correctly. You can check the correctness of the imported data as follows:

• Select * from t2
• Click on the first cell in the grid results
• Right-click on the same cell and select View Cell In ... > Image Viewer
• Result: a new tab should open with the image of a butterfly on a flower

@QA: please test import binary data to every database type that supports some form of binary data. Please import CSV, XLS and XLSX files, and verify that the binary data is imported correctly.

@Nhi, The fields of CSV file/existing table data has length more than 32767 characters. And Microsoft excel supports only 32767 characters per cell. If we try to add more than 32767 characters in excel cell, data gets truncated and result of importing same file data will be incorrect.

[Nhi]: Noted. Thanks, Ajit.

@Documentation: Please add this to the documentation.

@Nhi, Derby database insert query for BLOB datatype only supports small data. If try to insert large data it throws below error as "Caused by: ERROR 54002: Error for batch element #0: A string constant starting with 'x'ffd8ffe000104a46494600010101004800480000ffe2021c4943435f50&' is too long." with all transaction types. 

After looking into code I found that if we add support of derby in private boolean usePreparedStatement(ConnectionProperties cp) {

method of class ImportThread and select transaction type Batch, it work fine.

Is there any impact on other code, if we add derby database support in usePreparedStatement method to work import large data?

[Nhi] Should not have an impact on other code.

Refer below link for "Microsoft excel supports only 32767 characters per cell. If we try to add more than 32767 characters in excel cell, data gets truncated and result of importing same file data will be incorrect".

https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Office_2010

Thanks Juhi.

Hi @Ajit,

Found issues in Vertica Database:

Note: For large binary data, referred Nhi's attached export-blob.csv and export-blob.xlsx files 

1. a) Binary datatype --> CSV --> Importing small binary data (Binary value cannot exceed 65000)

•  FULL worked fine
• Batch imported 15 rows instead of 18 and throws attached error.png : (Issue) 

Also for imported 15 rows using Batch transaction type, data gets lost because it displays different value in the table after import.

•  Threshold worked same as Batch (Issue)

1.b) Binary datatype --> Excel --> Importing small binary data

• Full, Batch, Threshold worked fine

1.c) Binary datatype --> Excel --> Importing long binary data

• Full worked fine
• Batch, Threshold doesn’t throw error but data got lost (Issue)

2.a) Varbinary datatype --> Excel --> Importing Varbinary data

• Full worked fine
• Batch, Threshold doesn’t throw error but data got lost (Issue)

3.a) Long Varbinary datatype --> Excel --> Importing Long Varbinary data

• Full throws error of limit 65000 octets
• Batch worked fine
• Threshold worked fine

@Tom @Nhi,  In Vertica database if hex data has only numbers, then below code gets execute while importing data from file and imported data will be incorrect. Class ImportThread.java line no: 1353

if (NumberUtils.isNumber(columnInfo) && !(columnInfo.contains("x") || columnInfo.contains("X"))) {
   if (columnInfo.contains(".")) {
      stmt.setBytes(idx, AQHexUtils.hexToByte(Long.toHexString(Double.doubleToRawLongBits(Double.valueOf(columnInfo)))));
   }
   else {
      BigInteger bigInt = new BigInteger(columnInfo);
      stmt.setBytes(idx, bigInt.toByteArray());
   } 

}

Is there any impact on any other functionally, if we refactor this code as below?

stmt.setBytes(idx, AQHexUtils.hexToByte(columnObject.toString()));

[Nhi] Should be fine.

@Tom, @Nhi  Hive : Tools > Import Data menu is disabled for Hive database, We are not able to test import data in Hive databases.

Could you help us to enable menu to test import data?

Hi Ajit,

Lets not worry about Hive for this issue as the hives that haven't crashed don't allow acid transactions.

Thanks,

Tom

@Tom, @Nhi We are not able to batch import for BLOB data in an Interbase database. We have tried below code changes.

Added support of Interbase in usePreparedStatement method of ImportThread.java

Expression:

1.stmt.setBlob(idx, new SerialBlob(bytes));

Result : import --> Error: Row: 3 -- javax.sql.rowset.serial.SerialBlob cannot be cast to java.lang.Long.

2.stmt.setBytes(idx,bytes);

Result: import runs continuously.

3.ByteArrayInputStreamis= new ByteArrayInputStream(bytes);

        stmt.setBinaryStream(idx,is,bytes.length);

Result: import runs continuously.

4.ByteArrayInputStreamis= new ByteArrayInputStream(bytes);

          stmt.setBinaryStream(idx,is);

Result: Import runs successfully, but blob columns will have empty string.

Could you help us to find the correct way to batch import for BLOB data in an Interbase database?

@Ajit: Regarding InterBase, I've experimented and it works with stmt.setBinaryStream(idx, is, length). There are other issues that needs to be tweaked in order for it to work. Please see InterBase-Blob-Import.patch. Here I did a prototype and was able to successfully import the export-blob.csv file. Please use this patch as you see fit, but be sure to test it first. Also note that you'll also need to make similar changes in CoreLibImportThread and ImportExcelImportThread.

Functional Review:

I was trying to import CLOB into an Oracle database: Oracle 172.24.1.8 v12.2.0.2.0, Schema = TOM, Table = test_clob

When importing the export-small-blob.csv and export-small-blob.xlsx in BATCH mode, the content was imported with single quote characters around the CLOB values, which is incorrect. When I do the same import in FULL mode, it is imported correctly.

When importing the export-blob.csv file, the CLOB values are imported as TO_CLOB('ffd8fe...'), which is incorrect. The correct value should be ffd8fe...

I didn't test against other databases. Please be sure to test import of CLOB against other databases.

[Nhi] Looks good.

@Tom, @Nhi Teradata basebase was working fine for BLOB/CLOB datatype, but recently started throwing below error.

Hi Ajit,

Please add your test cases in the issue so I can debug them. 

Thanks, 

Tom

Hi Juhi,

I think this is because the database tom ran out of space. I increased the size and it now imports. Please make sure that you clean up after testing with the blob data. It can consume a lot of space.

Thanks,
Tom

Yes Tom, It worked fine now.

Thanks,

Juhi

@nhi

I am unable to connect to  "MySQL 172.24.1.199 v8.0". Error screenshot. 

Hi Viraaj,

Try it now. For some reason it was stopped. 

Thanks, Tom

Hi Viraaj,

Only 50% of the automated test cases passed for me, even after multiple runs. Please see results here: https://idera.testrail.net/index.php?/runs/view/3047&group_by=cases:section_id&group_order=asc&group_id=20777

Hi All ,

1.) Done with Manual testing of this ticket.

2.) Added test-cases in Test-Rail mentioned in below link :-

'https://idera.testrail.net/index.php?/cases/view/244931&group_by=cases:section_id&group_order=asc&group_id=20757'

3.) Results of these test cases (for binary , varbinary , blob datatypes) for all the supported databases are prevailed in the Excel sheet , use the below link to see the results.

'https://docs.google.com/spreadsheets/d/1WiDhxhzjLvXSw-h3GkiWKM-JRRbNa4g6GvbH_cQtjOc/edit?usp=sharing'

Thanks,

Juhi

I am still not able to get all automated test cases to succeed: https://idera.testrail.net/index.php?/runs/view/3047&group_by=cases:section_id&group_order=asc&group_id=20777

Hi Tom, 

There is issue with Teradata. It has error like "No more room in database tom" 

[TC] I cleaned up some space.

Hi @Nhi,

I have made some code updates in  SVN #24105. Can you pls try it again ? 

[Nhi] All test cases succeeded now.

QE Comment:

We have verified the fix for import\export of BLOB datatype through ADS UI and Fluidshell both on  ADS v20.6.0-rc-2  for following Database Servers:

Above databases are verified for import & Export of all types of datatypes supported by ADS for particular database servers in following platforms :
--> Windows 
--> Ubuntu
--> Mac OS 

Testcase results  for same could be found at the below link:
https://idera.testrail.net/index.php?/cases/view/249516&group_by=cases:section_id&group_order=asc&group_id=21880