For 0.7.1 and a configuration of:

tables:
  - name: "table_foo"
    uri: "mysql://username:password@localhost:3306/database"
    #uri: "table_foo=mysql://username:password@localhost:3306/database"

In any case I get:

 Invalid table URI: cannot detect table extension from uri: mysql://username:password@server:port/databas

back as a result - how can I tell roapi to actually use either postgres or MySQL? In both cases the table is not identified

@houqp as requested, the initial PoC of using TableProvider trait instead of explicit MemTable, to enable direct queries on the source storage via datafusion.

Hi all,

I'm using the version 0.4.4 and the REST API and was able to successfully use roapi using a projection/sort/limit pattern with the following call: curl -v "http://127.0.0.1:8084/api/tables/cities?&columns=City,State&sort=LatD&limit=3"

Unfortunately, when I run more than once this call, I'm starting to receive such messages: {"code":400,"error":"query_execution","message":"Failed to execute query: Error during planning: No field named 'cities.LatD'. Valid fields are 'cities.City', 'cities.State'."}* Connection #0 to host 127.0.0.1 left intact

More, I can't successfully qualify the names of the columns, I tried many variationq but without success: curl -v "http://127.0.0.1:8084/api/tables/cities?&sort=LatD&columns=cities.City,cities.State&limit=3" which is misleading regarding that the error statement is claiming that the expected fields are 'cities.City', 'cities.State'.

Any direction will be welcome.

the problem as the follows: When I execute "pip install roapi-http": Collecting roapi-http Could not find a version that satisfies the requirement roapi-http (from versions: ) No matching distribution found for roapi-http so, how to fix it?

Currently ColumnQ uses rusoto

A lot has been done with Object stores on datafusion and I'm wondering if it could be interesting to move to: https://github.com/datafusion-contrib/datafusion-objectstore-s3

when toying around with the basic example from the main README document I observe HTTP URI resolution problems:

roapi-http --version
roapi-http 0.5.3
roapi-http --table "uk_cities=https://raw.githubusercontent.com/roapi/roapi/main/test_data/uk_cities_with_headers.csv"
Error: failed to lookup address information: nodename nor servname provided, or not known


# strangely - this fails with the same error when running locally:
wget https://raw.githubusercontent.com/roapi/roapi/main/test_data/uk_cities_with_headers.csv
roapi-http --table "uk_cities=uk_cities_with_headers.csv"
Error: failed to lookup address information: nodename nor servname provided, or not known

when running natively on a Catalina OsX.

However, when executed inside your docker container - it works just fine.

Hello, I try to compile roapi-http from the source zip

I have an error when I compile

cargo install --verbose --path ./roapi-http --bin roapi-http building ... ...

error[E0053]: method `error_response` has an incompatible type for trait                                                              │
  --> /usr/local/cargo/git/checkouts/actix-extras-3214827614a42f65/ab3bdb6/actix-cors/src/error.rs:53:5                               │
   |                                                                                                                                  │
53 |     fn error_response(&self) -> HttpResponse {                                                                                   │
   |     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `BaseHttpResponse`, found struct `actix_web::HttpResponse`          │
   |                                                                                                                                  │
   = note: expected fn pointer `fn(&CorsError) -> BaseHttpResponse<actix_web::dev::Body>`                                             │
              found fn pointer `fn(&CorsError) -> actix_web::HttpResponse`                                                            │
                                                                                                                                      │
error: aborting due to previous error                                                                                                 │
                                                                                                                                      │
For more information about this error, try `rustc --explain E0053`.                                                                   │
error: could not compile `actix-cors`                                                                                                 │

Use example

curl -X POST http://172.24.16.1:8080/api/table \
   -H 'Content-Type: application/json' \
   -d '[
    {
        "tableName": "uk_cities2",
        "uri": "./test_data/uk_cities_with_headers.csv"
    }
]'

https://tech.marksblogg.com/roapi-rust-data-api.html mentioned that changing logging level from info to error resulted in 50% speed boost, this is certainly not expected :(

The upgrade that came with the SIMD build in the Dockerfile #81, broke the columnq and roapi-http builds. I tried fixing this in multiple ways but in the end upgrading the container to a more recent version and using rustls instead of native-tls (openssl) seems to work. I am not sure if my solution is acceptable, but I am not sure how to fix it otherwise.

I tried combining this with #31 but I only could get the x86_64 build to work.

When I tried to load a Delta table with partitions, it throws the following exception

Error: DeltaTable error: Failed to apply transaction log: Invalid JSON in log record Caused by: 0: Failed to apply transaction log: Invalid JSON in log record 1: Invalid JSON in log record 2: invalid type: null, expected a string at line 1 column 195

I believe it fails because the table commit log has an entry

{"add":{"path":"file_year=__HIVE_DEFAULT_PARTITION__/file_month=__HIVE_DEFAULT_PARTITION__/part-00000-4cfe8fb8-3905-4ae6-8b44-e7612753064c.c000.snappy.parquet","partitionValues":{"file_year":null,"file_month":null},"size":3331,"modificationTime":1628075908000,"dataChange":true}}

http rest api give message: Failed to execute query: Arrow error: External error: Arrow error: External error: Execution error: Arrow error: External error: Internal error: Data type LargeUtf8 not supported for scalar operation 'like' on string array. This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker

Maybe DataFusion not support this.

Currently all queries load the results in to memory

//in columnq/query/grapqhl.rs
pub async fn exec_query(
    dfctx: &datafusion::execution::context::SessionContext,
    q: &str,
) -> Result<Vec<arrow::record_batch::RecordBatch>, QueryError> {
    query_to_df(dfctx, q)?
        .collect()
        .await
        .map_err(QueryError::query_exec)
}
// columnq/query/rest.ts
pub async fn query_table(
    dfctx: &datafusion::execution::context::SessionContext,
    table_name: &str,
    params: &HashMap<String, String>,
) -> Result<Vec<RecordBatch>, QueryError> {
    let df = table_query_to_df(dfctx, table_name, params)?;
    df.collect().await.map_err(QueryError::query_exec)
}

So all this queries use a method called collect which as per docs loads the queried results in to memory.

Convert the logical plan represented by this DataFrame into a physical plan and execute it, collecting all resulting batches into memory Executes this DataFrame and collects all results into a vector of RecordBatch.

There is another method called collect_partitioned which does not load into the memory but return the result Result<Vec<Vec<RecordBatch>>>

xecutes this DataFrame and collects all results into a vector of vector of RecordBatch maintaining the input partitioning.

So can I create pr changing current query functions with collect_partitioned method or can you just any other alternatives to perform queries while not loading them into memory? thank you

I get an error when I try to use a delta table with partitions.

I can reproduce the error with this sample data:

df = pd.read_csv( "https://raw.githubusercontent.com/quankiquanki/skytrax-reviews-dataset/master/data/airport.csv") cols = df.columns df["year"] = pd.to_datetime(df["date"]).dt.year df = df[cols.insert(0, "year")]

write_deltalake("./data/airport1", df, partition_by=["year"]) df = df[cols.insert(len(cols), "year")] write_deltalake("./data/airport2", df, partition_by=["year"])

Request: localhost:8084/api/tables/airport1?columns=year Returns the column "airport_name"

Request: localhost:8084/api/tables/airport2?columns=year Returns an error: {"code":400,"error":"query_execution","message":"Failed to execute query: Arrow error: Invalid argument error: column types must match schema types, expected Dictionary(UInt16, Utf8) but found Dictionary(UInt16, Int64) at column index 0"}

Hi there

This project really looks promising! I have one thing I think should be changed: the SQL Endpoint does not seem to support parameters - at least it's not documented. That encourages users to create their SQL by using string concatenation which is not good from a security perspective (SQL injection). I propose to either use JSON Body:

{
"sql": "SELECT * FROM table where name = $name",
"parameters": {"$name": "something that is properly escaped\" drop table table"}
}

Or make use of headers:

POST URLTOTABLE
X-SQL-Parameter-Name: something that is properly escaped" drop table table

SELECT * FROM table where name = $name

What do you think?

s3 urls were not working when use_memory_table is set to false. It was returning the following error:

Error: DataFusion error: Internal error: No suitable object store found for s3://roapitest/blogs_flattened.parquet. This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker

So I went through the datafusion repo, did a little bit of debugging then I had found out that, While using use_memory_table: false it was using a function from datafusion called infer_schema which in turn uses object_store which is actually causing the error. object_store is a dashmap containing the schema info of path,s3,hdfs urls. Only localfile system urls are present in object_store. We have to manually configure s3 urls with bucket name using a function in SessionContext in RuntimeEnv called register_store. I was able to use register_store and the required functionality to use s3 without loading into the memory seems to work, but in columnq there is global SessionContext and when tables in loaded for example in parquet file in columnq/src/table/parquet.rs has a local SessionContext.

//in columnq/src/columnq.rs
pub fn new_with_config(config: SessionConfig) -> Self {
        let dfctx = SessionContext::with_config(config);
        let bucket_name = "roapi-test";
        let region = std::env::var("AWS_REGION").unwrap();
        let endpoint = std::env::var("AWS_ENDPOINT_URL").unwrap();
        let secret_access_key = std::env::var("AWS_SECRET_ACCESS_KEY").unwrap();
        let access_key_id = std::env::var("AWS_ACCESS_KEY_ID").unwrap();
        let s3_builder = AmazonS3Builder::new()
            .with_access_key_id(access_key_id)
            .with_bucket_name(bucket_name)
            .with_endpoint(endpoint)
            .with_region(region)
            .with_secret_access_key(secret_access_key)
            .build()
            .map_err(|e| {
                println!("{:?}", e);
                ColumnQError::MissingOption
            })
            .unwrap();
        dfctx.runtime_env().object_store_registry.register_store(
            "s3",
            "roapi-test",
            Arc::new(s3_builder),
        );
        let schema_map = HashMap::<String, arrow::datatypes::SchemaRef>::new();
        Self {
            dfctx,
            schema_map,
            kv_catalog: HashMap::new(),
        }
    }

//in columnq/src/table/parquet.rs
pub async fn to_datafusion_table(t: &TableSource) -> Result<Arc<dyn TableProvider>, ColumnQError> {
    let opt = t
        .option
        .clone()
        .unwrap_or_else(|| TableLoadOption::parquet(TableOptionParquet::default()));
    let TableOptionParquet { use_memory_table } = opt.as_parquet()?;

    if *use_memory_table {
        to_mem_table(t).await
    } else {
        let table_url = ListingTableUrl::parse(t.get_uri_str())?;
        let options = ListingOptions::new(Arc::new(ParquetFormat::default()));

        let schemaref = match &t.schema {
            Some(s) => Arc::new(s.into()),
            None => {
                let ctx = SessionContext::new();
               
                let bucket_name = "roapi-test";
                let region = std::env::var("AWS_REGION").unwrap();
                let endpoint = std::env::var("AWS_ENDPOINT_URL").unwrap();
                let secret_access_key = std::env::var("AWS_SECRET_ACCESS_KEY").unwrap();
                let access_key_id = std::env::var("AWS_ACCESS_KEY_ID").unwrap();
                let s3_builder = AmazonS3Builder::new()
                    .with_access_key_id(access_key_id)
                    .with_bucket_name(bucket_name)
                    .with_endpoint(endpoint)
                    .with_region(region)
                    .with_secret_access_key(secret_access_key)
                    .build()
                    .map_err(|e| {
                        println!("{:?}", e);
                        ColumnQError::MissingOption
                    })?;
                ctx.runtime_env().object_store_registry.register_store(
                    "s3",
                    "roapi-test",
                    Arc::new(s3_builder),
                );
                let s = options.infer_schema(&ctx.state(), &table_url).await?;
                println!("6");
                s
            }
        };

        let table_config = ListingTableConfig::new(table_url)
            .with_listing_options(options)
            .with_schema(schemaref);
        Ok(Arc::new(ListingTable::try_new(table_config)?))
    }
}

if you can see I have to use register_store function twice into order make s3 urls to work only for parquet files. If want it to work for csv files, I have to use the function thrice. Which I think is not efficient.

Do you think is there a better way to do it like having a single global SessionContext?