CSV

Working with Vector Data and Oracle SQL Command Line SQLcl

Posted on Updated on

Working with Vector data has been a hot topic over the past couple of years. There are lots and articles and blog posts showing some basic functionality and similarity search using Vectors. Most of these articles demonstrate the basics, but then you start working with Vector data and perhaps having to move this data between schemas or between databases you could face a little bit of a challenge. One of these challenges or perhaps compatibility issues is with the format of the Vector data in the output/input file. In the example below I’m going to use CSV file to illustrate the issue, and specifically with using Oracle SQLcl and the export/import to/from CSV file feature.

It should be noted that the issued described below might not be an issue in a future release of SQLcl. I’ve reported this issue and a bug report has been filled. So fingers cross it will be fixed soon and even back ported. So the workaround or fix illustrated below might not be necessary at some point in the future, but until such time the workaround below is required.

The basic secenario that I faced involved exporting a table, containing Vector data, and importing it into a different (empty) table in the same schema. The export feature seemed to work correctly but I get an error when importing the file. Something about the work data type etc. It was complaining about the Vector column format. After a little investigation, the export command exported the data but the Vector column did not have quotes around the data. The import command expected the Vector data to be in quotes. So there is a mismatch between the export and import commands for Vector data.

Here is a bit more detail on how I encountered this issue.

I created a table to store Wine Reviews, Imported the Wine Review data set. added a column to store the Vector Data, added the Vector data using a model I’d already imported. I then exported the Wine Reviews with the Vector data to CSV. I unloaded the table using the following SQL Command Line function UNLOAD

sql> unload wine_reviews

This created a file in my working directory called WINEREVIEWS_TABLE.csv

I then created a new version of the table with a different name, WINEREVIEWS_VEC, and used the LOAD function to load the CSV data into the the newly created table.

sql> load WINEREVIEWS_VEC WINEREVIEWS_TABLE.csv

This gives me the following error.

#ERROR Insert failed in batch rows  1  through  50
#ERROR ORA-51804: Invalid syntax for VECTOR value. Must be a string of the form
DENSE vector: '[<number> , <number> ..., <number>]'
SPARSE vector: '[<total dimension count (optional)>, [<sparse index array>], [<sparse dimension value array>]]'.

What this error means is the Vector data is missing a quote at the beginning and end of the vector data.

So what can we do to fix this issue and move forward with import and being able to use the data.

In my case I used the ‘sed’ command, and here is the one line ‘sed’ command to add the quotes around the required field.

sed 's/,\(\[[^]]*\]\)/,"\1"/g' WINEREVIEWS_TABLE.csv > WINEREVIEWS_TABLE_vec.csv

No using he same LOAD function as previously used, the import/load works and is completed in a couple of seconds.

Before we end this post, lets have another look at the ‘sed’ command and what it does, as you can see it is a little complicated.

sed 's/,\(\[[^]]*\]\)/,"\1"/g' WINEREVIEWS_TABLE.csv > WINEREVIEWS_TABLE_vec.csv

The main focus of this command is to look for fields that are enclosed by square brackets [ ]. When you look at the CSV file, only the vector data is enclose in such brackets. what we need to do is to wrap this in quotes, as otherwise the comma in these brackets will be considered as field/column separators.

Breaking down the pattern ,\(\[[^]]*\]\)

So the whole thing matches: a comma, immediately followed by a bracketed block like [anything except ]], and captures that bracketed block (brackets included) as group 1.

The replacement ,"\1" puts back the comma, then wraps the captured bracket block in double quotes.

The g flag makes it do this for every match on the line, not just the first.

This entry was posted in SQL_CL, Vector Database, Vector Embeddings and tagged , , , .

Migrating SAS files to CSV and database

Posted on

Many organizations have been using SAS Software to analyse their data for many decades. As with most technologies organisations will move to alternative technologies are some point. Recently I’ve experienced this kind of move. In doing so any data stored in one format for the older technology needed to be moved or migrated to the newer technology. SAS Software can process data in a variety of format, one of which is their own internal formats. Thankfully Pandas in Python as a function to read such SAS files into a pandas dataframe, from which it can be saved into some alternative format such as CSV. The following code illustrates this conversion, where it will migrate all the SAS files in a particular directory, converts them to CSV and saves the new file in a destination directory. It also copies any existing CSV files to the new destination, while ignore any other files. The following code is helpful for batch processing of files.

import os
import pandas as pd

#define the Source and Destination directories
source_dir='/Users/brendan.tierney/Dropbox/4-Datasets/SAS_Data_Sets'
dest_dir=source_dir+'/csv'

#What file extensions to read and convert
file_ext='.sas7bdat'

#Create output directory if it does not already exist
if not os.path.exists(dest_dir):
    os.mkdir(dest_dir)
else:  #If directory exists, delete all files
    for filename in os.listdir(source_dir):
        os.remove(filename)

#Process each file
for filename in os.listdir(source_dir):
    #Process the SAS file
    if filename.endswith(file_ext):
        print('.processing file :',filename) 
        print('...converting file to csv')
        df=pd.read_sas(os.path.join(source_dir, filename))
        df.to_csv(os.path.join(dest_dir, filename))
        print('.....finished creating CSV file')
    elif filename.endswith('csv'):
        #Copy any CSV files to the Destination Directory
        print('.copying CSV file')
        cmd_copy='cp '+os.path.join(source_dir, filename)+' '+os.path.join(dest_dir, filename)
        os.system(cmd_copy)
        print('.....finished copying CSV file')
    else:
        #Ignore any other type of files
        print('.ignoring file :',filename)

print('--Finished--')

That’s it. All the files have now been converted and located in the destination directory.

For most, the above might be all you need, but sometimes you’ll need to move the the newer technology. In most cases the newer technology will easily use the CSV files. But in some instance your final destination might be a database. In my scenarios I use the CSV2DB app developed by Gerald Venzi. You can download the code from GitHub. You can use this to load CSV files into Oracle, MySQL, PostgreSQL, SQL Server and Db2 databases. Here’s and example of the command line to load into an Oracle Database.

csv2db load -f /Users/brendan.tierney/Dropbox/4-Datasets/SAS_Data_Sets/csv -t pva97nk -u analytics -p analytics  -d PDB1

This entry was posted in Analytics, CSV, Python, SAS and tagged , , .