Collecting column data from a ColumnarRowset into a vector of values

[waynehsmith]

Since AnyColumnView does not implement the Clone trait, is there another recommended mechanism to collect the values of a row into a vector?

[pacman82]

Hello @waynehsmith ,

thanks for giving this crate a spin. What you are trying to do is likely not supported well at the moment, depending on what exactly you are trying to achieve it's maybe still possible though. It will however at least be akward at until row wise bulk fetch is implemented.

So let's start with Vec. A single Vec of rows is, unsurprisingly, a rowwise datastructure. The first usecases driving this crate were in the realm of data science in which columnar data structures are usually preferable. So this is what is currently implemented. Row wise bulk fetches are very much on the roadmap, but not implemented as of now.

That being said there are three things, which might work for you.

1. There is a way of rowwise iteration using Cursor::next_row instead of binding a buffer. I would only recommend it in usecases though in which performance is not an issue, as many ODBC drivers might perform a roundtrip to the data source for each row or even field.
2. You can of course bind a columnar buffer and copy the data to row wise data structure. However that raises a question which data structure represents a row. This crate does not provide a type which can represent any buffered value, or any row type. Yet in case you should know the table layout at compile time, the below code example might be useful. I tested it for you here:

   odbc-api/odbc-api/tests/integration.rs

   Line 2586 in 1d3aed6

   fn fill_vec_of_rows(profile: &Profile) {

3. If you are okay with representing your individual rows as a Vec (which would result in a Vec<Vec>, try using TextRowSet.

Kindly let me know if I understood your question correctly and if any of this is helpful to you. If not please let me know more about your usecase.

    let insert_sql = format!("INSERT INTO {} (a,b) VALUES ('A', 1), ('B',2)", table_name);
    conn.execute(&insert_sql, ()).unwrap();

    // Now that the table is created and filled with some values lets query it and put its contents
    // into a `Vec`

    let query_sql = format!("SELECT a,b FROM {}", table_name);
    let cursor = conn.execute(&query_sql, ()).unwrap().unwrap();
    let buf_desc = [
        BufferDescription {
            nullable: true,
            kind: BufferKind::Text { max_str_len: 50 },
        },
        BufferDescription {
            nullable: false,
            kind: BufferKind::I32,
        },
    ];

    let buffer = ColumnarRowSet::new(1, buf_desc.iter().copied());
    let mut cursor = cursor.bind_buffer(buffer).unwrap();

    let mut actual = Vec::new();

    while let Some(batch) = cursor.fetch().unwrap() {
        // Extract first column known to contain text
        let mut col_a = match batch.column(0) {
            AnyColumnView::Text(col) => col,
            _ => panic!("First column is supposed to be text"),
        };

        // Extract second column known to contain non nullable i32
        let col_b = match batch.column(1) {
            AnyColumnView::I32(col) => col,
            _ => panic!("Secord column is supposed to be an i32"),
        };

        for row in 0..batch.num_rows() {
            let a = col_a
                .next()
                .unwrap()
                .map(|bytes| str::from_utf8(bytes).unwrap().to_owned());
            let b = col_b[row];
            actual.push((a, b))
        }
    }

    assert_eq!(
        actual,
        [(Some("A".to_string()), 1), (Some("B".to_string()), 2)]
    )
}

[pacman82]

Hello @waynehsmith ,

reviving this thread to notify you that arrow-odbc has been released since. It may be helpful in your usecaes.

Cheers, Markus

[bbigras]

@pacman82 my use case would be to read row by row. something like:

for row in rows {
    let id: i16 = row.get(0)?;
    let name: &str = row.get(1)?;
    // ...
}

Would arrow-odbc be ideal for this?

Also, would it be easy to adapt arrow-odbc to iodbc?

[pacman82]

Contrary to other claims on the internet ODBC is working with columnar buffers by default. The safe abstractions in this library currently also all use columnar. One reason why columnar fetching is a good choice for fetching the data (even if you want to process row by row) is that each value in a column has the same type. This would not be the case for most rows however. Row wise fetching would require to provide the memory layout for each row in advance to ODBC.

Long story short the Vec<Row> interface users understandably want, would be an instant pessimiation. At least doubling memory usage and requiring one copy of the entire buffer. Not something I want for this crate.

arrow-odbc is intended to be used by Data Engineers and Data Scientists. It would excel in making the results available in other languages. The reason I suggest the usage of arrow-odbc outside of this usecase is, that it is a higher level way of fetching and processing data thereas this crate is still very low-level and requires people to think about memory layouts and buffers and stuff. So most users might be happier with something which is slightly easier to use.

Would arrow-odbc be ideal for this?

So, no. arrow-odbc isn't ideal for this. It's still columnar. Yet it is the only higher level way of fetching data which is not stupidly slow (like 500x or 1000x times slower). There are slow implementations out there (e.g. odbc-iter), the all just use (in the end) SQLGetData to get results row by row (or even field by field) from the database.

I would suggest fetching data columnar, and accessing it row first if the situation requires it. You can still iterate in over an arrow array in row first manner. Just requires switching to indices in a for loop. Arrays are still random access. So while I think that arrow-odbc might not be ideal. It might still be good enough. arrow-odbc will give you RecordBatches. Just look at the arrow documentation and see if you like what you can do with them.

Also, would it be easy to adapt arrow-odbc to iodbc?

I do not know. It could be enough to explicitly specify the odbc-api dependency with the iodbc feature. Either that works or most likely nothing will. Currently I do not see any reason why it should not work though.

[bbigras]

I would suggest fetching data columnar, and accessing it row first if the situation requires it. You can still iterate in over an arrow array in row first manner. Just requires switching to indices in a for loop.

Like this?

let cursor = conn.execute(&query_sql, ()).unwrap().unwrap();

let buf_desc = [
    BufferDescription {
        nullable: false,
        kind: BufferKind::Text { max_str_len: 250 },
    },
    BufferDescription {
        nullable: true,
        kind: BufferKind::I32,
    },
];

let buffer = ColumnarAnyBuffer::try_from_description(2, buf_desc.iter().copied()).unwrap();
let mut cursor = cursor.bind_buffer(buffer).unwrap();

while let Some(batch) = cursor.fetch().unwrap() {
    let mut col_a = batch.column(0).as_text_view().unwrap().iter();
    let mut col_b = i32::as_nullable_slice(batch.column(1)).unwrap();

    for _i in 0..batch.num_rows() {
        let v1 = col_a.next().unwrap();
        let v2 = col_b.next().unwrap();
        dbg!((&v1, &v2));
    }
}

or maybe with multizip:

use itertools::multizip;
for (_i, v1, v2) in multizip((0..batch.num_rows(), col_a.iter(), col_b)) {
    dbg!((&v1 &v2));
}

[pacman82]

This is using the raw buffer instead of arrow-odbc, but yes, like this. Hm, really should allow random access on these columns 😅