COPY data remotely via psql client is complaining about privileges

CrateDB
1

I just installed cratedb 4.5 in my lab and from a remote location I am trying INSERT some data using the psql client:

[root@raptor1 results]# psql -hXXX.XXX.XXX.XXX -dwebservicetests -Uuser -c "\copy webservicetests.webperformance FROM [/path/]sample.csv;"
Password for user user:
ERROR:  Missing privilege for user 'user'

The CSV has the header line. I assigned DDL,DQL,DML to the user for the table “webservicetests.webperformance” so I am not sure why I am getting this. Am I not supposed to be using the psql client to INSERT data. If that is not the case, please point me to the right direction.

Thanks

2

I tried to import a part of the file locally using cr. This is what I am getting:

cr>
cr> COPY webservicetests.webperformance FROM ‘file:///root/downloads/aydin.json’;
COPY OK, 0 rows affected (0.004 sec)
cr>

But I am still not seeing any data. Any help is greatly appreciated.

3

Hi @asavran

Am I not supposed to be using the psql client to INSERT data. If that is not the case, please point me to the right direction.

It should work with psql as well, but if you are already further with crash, let’s go one from there

Could you please add RETURN SUMMARY to the end of the COPY Statement like:

COPY webservicetests.webperformance FROM ‘file:///root/downloads/aydin.json’ RETURN SUMMARY;
4 
root@raptor1:/tmp# crash --host "10.10.10.195" --schema "webservicetests" --verbose
    +--------------------------+---------------+---------+-----------+---------+
    | server_url               | node_name     | version | connected | message |
    +--------------------------+---------------+---------+-----------+---------+
    | http://10.10.10.195:4200 | Punta Cornour | 4.5.0   | TRUE      | OK      |
    +--------------------------+---------------+---------+-----------+---------+
    CONNECT OK
    CLUSTER CHECK OK
    TYPES OF NODE CHECK OK
    cr> COPY webservicetests.webperformance FROM 'file:///tmp/abby.json' RETURN SUMMARY;
    +-----------------------------------------------------------+-----------------------+---------------+-------------+--------+
    | node                                                      | uri                   | success_count | error_count | errors |
    +-----------------------------------------------------------+-----------------------+---------------+-------------+--------+
    | {"id": "eBkTRkqQQXWc99bVnUZPow", "name": "Punta Cornour"} | file:///tmp/abby.json |            16 |           0 | {}     |
    +-----------------------------------------------------------+-----------------------+---------------+-------------+--------+
    COPY 1 row in set (0.143 sec)
    cr>

abby.json is a flat json file with 16 records. I think the above record shows that the insert was successful. Following are my observations:

1)
I had to connect to crash as user crate because whenever I try to connect to the same schema with my dedicated username I get the following error on crash:
root@raptor1:~# crash --host “10.10.10.195” --schema “webservicetests” --user “turanz” --verbose
Password:
±-------------------------±--------------±--------±----------±--------+
| server_url | node_name | version | connected | message |
±-------------------------±--------------±--------±----------±--------+
| http://10.10.10.195:4200 | Punta Cornour | 4.5.0 | TRUE | OK |
±-------------------------±--------------±--------±----------±--------+
CONNECT OK
SchemaUnknownException[Schema ‘sys’ unknown]

io.crate.exceptions.SchemaUnknownException: Schema 'sys' unknown
        at io.crate.user.Privileges.ensureUserHasPrivilege(Privileges.java:99)
        at io.crate.auth.AccessControlImpl$MaskSensitiveExceptions.visitSchemaScopeException(AccessControlImpl.java:701)
        at io.crate.auth.AccessControlImpl$MaskSensitiveExceptions.visitSchemaScopeException(AccessControlImpl.java:681)
        at io.crate.exceptions.SchemaScopeException.accept(SchemaScopeException.java:32)
        at io.crate.auth.AccessControlImpl.ensureMaySee(AccessControlImpl.java:132)
        at io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:128)
        at io.crate.rest.action.SqlHttpHandler.sendResponse(SqlHttpHandler.java:162)
        at io.crate.rest.action.SqlHttpHandler.lambda$channelRead0$0(SqlHttpHandler.java:118)
        at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
        at java.base/java.util.concurrent.CompletableFuture.uniWhenCompleteStage(CompletableFuture.java:883)
        at java.base/java.util.concurrent.CompletableFuture.whenComplete(CompletableFuture.java:2321)
        at io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:116)
        at io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:79)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.crate.protocols.http.HttpBlobHandler.channelRead0(HttpBlobHandler.java:166)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.crate.auth.HttpAuthUpstreamHandler.handleHttpChunk(HttpAuthUpstreamHandler.java:136)
        at io.crate.auth.HttpAuthUpstreamHandler.channelRead0(HttpAuthUpstreamHandler.java:85)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
        at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795)
        at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:480)
        at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378)
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
        at java.base/java.lang.Thread.run(Thread.java:831)

SchemaUnknownException[Schema 'sys' unknown]

io.crate.exceptions.SchemaUnknownException: Schema 'sys' unknown
        at io.crate.user.Privileges.ensureUserHasPrivilege(Privileges.java:99)
        at io.crate.auth.AccessControlImpl$MaskSensitiveExceptions.visitSchemaScopeException(AccessControlImpl.java:701)
        at io.crate.auth.AccessControlImpl$MaskSensitiveExceptions.visitSchemaScopeException(AccessControlImpl.java:681)
        at io.crate.exceptions.SchemaScopeException.accept(SchemaScopeException.java:32)
        at io.crate.auth.AccessControlImpl.ensureMaySee(AccessControlImpl.java:132)
        at io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:128)
        at io.crate.rest.action.SqlHttpHandler.sendResponse(SqlHttpHandler.java:162)
        at io.crate.rest.action.SqlHttpHandler.lambda$channelRead0$0(SqlHttpHandler.java:118)
        at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
        at java.base/java.util.concurrent.CompletableFuture.uniWhenCompleteStage(CompletableFuture.java:883)
        at java.base/java.util.concurrent.CompletableFuture.whenComplete(CompletableFuture.java:2321)
        at io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:116)
        at io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:79)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.crate.protocols.http.HttpBlobHandler.channelRead0(HttpBlobHandler.java:166)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.crate.auth.HttpAuthUpstreamHandler.handleHttpChunk(HttpAuthUpstreamHandler.java:136)
        at io.crate.auth.HttpAuthUpstreamHandler.channelRead0(HttpAuthUpstreamHandler.java:85)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
        at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795)
        at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:480)
        at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378)
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
        at java.base/java.lang.Thread.run(Thread.java:831)

This forced me to use the username “crate” to do this.

2)
I can enter “SELECT * FROM webservicetests.webperformance;” under crash and get a return successfully. But I cannot enter the same command under the “Admin UI”. I get the following:

io.crate.exceptions.SQLParseException: empty String
	at io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:137)
	at io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:126)
	at io.crate.rest.action.SqlHttpHandler.sendResponse(SqlHttpHandler.java:162)
	at io.crate.rest.action.SqlHttpHandler.lambda$channelRead0$0(SqlHttpHandler.java:118)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
	at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
	at java.base/java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:2158)
	at io.crate.rest.action.RestResultSetReceiver.fail(RestResultSetReceiver.java:89)
	at io.crate.protocols.postgres.RetryOnFailureResultReceiver.fail(RetryOnFailureResultReceiver.java:115)
	at io.crate.action.sql.RowConsumerToResultReceiver.lambda$consumeIt$1(RowConsumerToResultReceiver.java:92)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
	at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
	at java.base/java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:2158)
	at io.crate.action.FutureActionListener.onFailure(FutureActionListener.java:53)
	at org.elasticsearch.action.ActionListenerResponseHandler.handleException(ActionListenerResponseHandler.java:62)
	at org.elasticsearch.transport.TransportService$TimeoutResponseHandler.handleException(TransportService.java:1048)
	at org.elasticsearch.transport.TransportService$DirectResponseChannel.processException(TransportService.java:1165)
	at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1139)
	at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:66)
	at io.crate.execution.support.NodeActionRequestHandler.lambda$messageReceived$0(NodeActionRequestHandler.java:59)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
	at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1769)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
	at java.base/java.lang.Thread.run(Thread.java:831)
Caused by: java.lang.NumberFormatException: empty String
	at java.base/jdk.internal.math.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1842)
	at java.base/jdk.internal.math.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
	at java.base/java.lang.Double.parseDouble(Double.java:556)
	at org.elasticsearch.common.xcontent.support.AbstractXContentParser.intValue(AbstractXContentParser.java:133)
	at org.elasticsearch.common.xcontent.support.AbstractXContentParser.intValue(AbstractXContentParser.java:125)
	at io.crate.expression.reference.doc.lucene.SourceParser.parseByType(SourceParser.java:180)
	at io.crate.expression.reference.doc.lucene.SourceParser.parseValue(SourceParser.java:162)
	at io.crate.expression.reference.doc.lucene.SourceParser.parseObject(SourceParser.java:140)
	at io.crate.expression.reference.doc.lucene.SourceParser.parse(SourceParser.java:101)
	at io.crate.expression.reference.doc.lucene.SourceLookup.ensureSourceParsed(SourceLookup.java:81)
	at io.crate.expression.reference.doc.lucene.SourceLookup.get(SourceLookup.java:64)
	at io.crate.expression.reference.doc.lucene.DocCollectorExpression$ChildDocCollectorExpression.value(DocCollectorExpression.java:98)
	at io.crate.expression.InputRow.get(InputRow.java:46)
	at io.crate.execution.engine.distribution.StreamBucket$Builder.add(StreamBucket.java:76)
	at io.crate.execution.engine.fetch.FetchCollector.collect(FetchCollector.java:105)
	at io.crate.execution.engine.fetch.NodeFetchOperation.lambda$doFetch$3(NodeFetchOperation.java:193)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1764)
	... 3 more

I am connected to Admin UI as “crate”.
Please note that I granted DDL,DML and DQL roles to turanz username for “webservicetests.webperformance”

3)

My original timestamp in json file was in epoch. After the JSON import, when I pull the timestamp column using “SELECT timestamp FROM [tablename]” under crash, I get all the dates from 1970-01-19 17:25:08.071+00.

Here is the return from the Admin UI for “SELECT “timestamp” from [tablename]”:
timestamp
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908070 (1970-01-19T17:25:08.070Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908070 (1970-01-19T17:25:08.070Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
1617908071 (1970-01-19T17:25:08.071Z)
These epoch records are from yesterday April 8th 2021.
I am not sure if my table definitions are wrong:
CREATE TABLE webservicetests.webperformance (
“metric_name” STRING,
“timestamp” TIMESTAMP,
“metric_value” REAL,
“check” STRING,
“error” STRING,
“error_code” INTEGER,
“expected_response” STRING,
“group” STRING,
“method” STRING,
“name” STRING,
“proto” STRING,
“scenario” STRING,
“service” STRING,
“status” INTEGER,
“subproto” STRING,
“tls_version” STRING,
“url” STRING,
“extra_tags” STRING,
“agent_hostname” STRING,
“agent_ipaddress” STRING,
“agent_casetype” STRING,
“agent_casename” STRING,
“month” AS date_trunc(‘month’, “timestamp”)
)
PARTITIONED BY (“month”) WITH (column_policy = ‘dynamic’);

5

I tried the same import with CSV… I imported the same file after converting it from json to csv… This was unsuccessful:

cr> COPY webservicetests.webperformance FROM 'file:///tmp/abby.csv' RETURN SUMMARY;
+-----------------------------------------------------------+----------------------+---------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| node                                                      | uri                  | success_count | error_count | errors                                                                                                                                                                                                                                            |
+-----------------------------------------------------------+----------------------+---------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"id": "eBkTRkqQQXWc99bVnUZPow", "name": "Punta Cornour"} | file:///tmp/abby.csv |             0 |          16 | {"class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap')": {"count": 16, "line_numbers": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]}} |
+-----------------------------------------------------------+----------------------+---------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
COPY 1 row in set (0.006 sec)

Please note that I tried to this as “crate” user. I don’t have any primary keys or constraints on this table.

6

Hello @proddata

I am not sure if you had a chance to review my updates but I wanted to correct the 3rd observation about the timestamp. I appended 3 zeroes at the end of my timestamps to make it epoch-msec and after that I was able to see the correct timestamp.

7

CrateDBs privileges by default are very restrictive - i.e. if you only grant privileges to a specific table, this really means, that that user can only read that table (excluding some system tables within sys or pg_catalog schema). We are currently looking into reworking default privileges.

crash is running a query on sys.cluster to retrieve some session information. that is why you see there error. querying on the table should work anyway.

I can enter “SELECT * FROM webservicetests.webperformance;” under crash and get a return successfully. But I cannot enter the same command under the “Admin UI”. I get the following:

This seems strange. you should be able to query within the Admin-UI as well.

I tried the same import with CSV… I imported the same file after converting it from json to csv… This was unsuccessful:

JSON definitely is preferred, as it at least follows certain standards.
be sure that the csv has a header row. Is month also part of the csv?
can you maybe share an example of the csv / json?

8

Hello @proddata

Month is not part of the CSV. Following is my table definition:

CREATE TABLE webservicetests.webperformance (
"metric_name" STRING,
"timestamp" TIMESTAMP WITHOUT TIME ZONE,
"metric_value" REAL,
"check" STRING,
"error" STRING,
"error_code" INTEGER,
"expected_response" STRING,
"group" STRING,
"method" STRING,
"name" STRING,
"proto" STRING,
"scenario" STRING,
"service" STRING,
"status" INTEGER,
"subproto" STRING,
"tls_version" STRING,
"url" STRING,
"extra_tags" STRING,
"agent_hostname" STRING,
"agent_ipaddress" STRING,
"agent_casetype" STRING,
"agent_casename" STRING,
"month" TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS date_trunc('month', "timestamp")
)
PARTITIONED BY ("month") WITH (column_policy = 'dynamic');

According to the online documentation, bulk COPY using psql client is not supported. Is this accurate? If it is not I will keep trying. Otherwise, I am thinking about using the http endpoint option to do bulk inserts remotely. What do you suggest? HTTP endpoint option is ok but it is complaining when I try to push the data without the “month” column. Actually, it is not giving any errors but the inserted row count reads a negative number and I cannot see the new rows in the table. I thought the “month” column was being generated on cratedb side automatically.

Also I was wondering if ingesting data via Kafka Connect JDBC is considered as bulk INSERT. Can you clarify this for me.

Thanks,

9

According to the online documentation, bulk COPY using psql client is not supported. Is this accurate? If it is not I will keep trying. Otherwise, I am thinking about using the http endpoint option to do bulk inserts remotely.

What do you mean with bulk COPY you can use psql as client to connect to CrateDB and also to send COPY FROM / COPY TO queries. If you use psql, crash, the Admin UI or any other client, doesn’t really matter. The COPY statement will be run on the cluster / nodes and is independent from the client.

CrateDB doesn’t support STDIN / STDOUT like PostgreSQL does however.

[1] COPY FROM — CrateDB: Reference
[2] Bulk inserts — CrateDB: How-Tos

I am thinking about using the http endpoint option to do bulk inserts remotely.
You can use the http-endpoint, but also inserting using mutliple values

are you referring to bulk operations with INSERT-Statements? you can also batch inserts by specifying multiple value-statements and/or use a client that is capable of prepared statements.
[3] https://crate.io/docs/crate/howtos/en/latest/performance/inserts/methods.html

Also I was wondering if ingesting data via Kafka Connect JDBC is considered as bulk INSERT. Can you clarify this for me.

Kafka connect typically uses batched statements.

10

Thank you @proddata

by bulk COPY, I meant using psql client to COPY multiple records from a CSV file. Also even if I give full privileges to my username for the relevant schema and sys schema, I am still running into the privilege issue with the psql remote client INSERT. I just tried HTTP endpoint option:

curl -sS -H 'Content-Type: application/json' -X POST 'UUUU:PPP@209.44.XX.XX:4200/_sql?error_trace=true' -d@- <<- EOF
> {"stmt": "INSERT INTO webservicetests.webperformance (metric_name,\"timestamp\",metric_value,check,error,error_code,expected_response,\"group\") VALUES (?,?,?,?,?,?,?,?)","bulk_args": [
> ["vus",1618232816000,1.000000,"","",,"",""],
> ["http_req_blocked",1618232816000,235.960781,"","",,"true",""]]
> }
> EOF
{"cols":[],"duration":1.637055,"results":[{"rowcount":-2},{"rowcount":-2}]}

For some reason I am getting -2 as the row count and not seeing the rows I inserted. This particular table has more columns but I am only inserting 8 columns. I tried to narrow the issue down. So when I try to do the insert using the first three columns only (metric_name,“timestamp”,metric_value), the insert works. When I add more columns I am running in to the negative row count issue.

{"error":{"message":"SQLParseException[Failed to parse source [{\"stmt\": \"INSERT INTO webservicetests.webperformance (metric_name,\\\"timestamp\\\",metric_value,check,error,error_code,expected_response) VALUES (?,?,?,?,?,?,?)\",\"bulk_args\": [[\"vus\",1618232816000,1.000000,\"\",\"\",,\"\"],[\"http_req_blocked\",1618232816000,235.960781,\"\",\"\",,\"\"]]}]]","code":4000},"error_trace":"io.crate.exceptions.SQLParseException: Failed to parse source [{\"stmt\": \"INSERT INTO webservicetests.webperformance (metric_name,\\\"timestamp\\\",metric_value,check,error,error_code,expected_response) VALUES (?,?,?,?,?,?,?)\",\"bulk_args\": [[\"vus\",1618232816000,1.000000,\"\",\"\",,\"\"],[\"http_req_blocked\",1618232816000,235.960781,\"\",\"\",,\"\"]]}]\n\tat io.crate.action.sql.parser.SQLRequestParser.parseSource(SQLRequestParser.java:87)\n\tat io.crate.rest.action.SqlHttpHandler.handleSQLRequest(SqlHttpHandler.java:194)\n\tat io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:115)\n\tat io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:79)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.crate.protocols.http.HttpBlobHandler.channelRead0(HttpBlobHandler.java:166)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.crate.auth.HttpAuthUpstreamHandler.handleHttpChunk(HttpAuthUpstreamHandler.java:136)\n\tat io.crate.auth.HttpAuthUpstreamHandler.channelRead0(HttpAuthUpstreamHandler.java:85)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)\n\tat io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)\n\tat io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795)\n\tat io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:480)\n\tat io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378)\n\tat io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)\n\tat io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)\n\tat java.base/java.lang.Thread.run(Thread.java:831)\nCaused by: com.fasterxml.jackson.core.JsonParseException: Unexpected character (',' (code 44)): expected a value\n at [Source: (org.elasticsearch.transport.netty4.ByteBufStreamInput); line: 1, column: 211]\n\tat com.fasterxml.jackson.core.JsonParser._constructError(JsonParser.java:1851)\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportError(ParserMinimalBase.java:707)\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportUnexpectedChar(ParserMinimalBase.java:632)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser._handleUnexpectedValue(UTF8StreamJsonParser.java:2650)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser._nextTokenNotInObject(UTF8StreamJsonParser.java:865)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser.nextToken(UTF8StreamJsonParser.java:757)\n\tat org.elasticsearch.common.xcontent.json.JsonXContentParser.nextToken(JsonXContentParser.java:53)\n\tat io.crate.action.sql.parser.SQLArgsParseElement.parseSubArray(SQLArgsParseElement.java:43)\n\tat io.crate.action.sql.parser.SQLBulkArgsParseElement.parseSubArrays(SQLBulkArgsParseElement.java:47)\n\tat io.crate.action.sql.parser.SQLBulkArgsParseElement.parse(SQLBulkArgsParseElement.java:39)\n\tat io.crate.action.sql.parser.SQLRequestParser.parse(SQLRequestParser.java:105)\n\tat io.crate.action.sql.parser.SQLRequestParser.parseSource(SQLRequestParser.java:77)\n\t... 45 more\n"}

Note: Error_code field is in integer so I did not put that field in double quotes when I typed it in the command.

Any clues?

11

psql also needs read priviliges on pg_catalog which is there for compatibility with native postgres clients.

12 
const axios = require('axios');

const crate_endpoint = "https://xxxx.cratedb.net:4200/_sql";

const body = {
    stmt : `INSERT INTO webservicetests.webperformance (metric_name,
        \"timestamp\",metric_value,check,error,error_code,expected_response,\"group\")
        VALUES (?,?,?,?,?,?,?,?)`,
    bulk_args : [
        ["vus",1618232816000,1.000000,"","",,"",""],
        ["http_req_blocked",1618232816000,235.960781,"","",,"true",""]
    ]
}; 

const config = {
    auth: {
        username: 'xxx',
        password: 'xxxxx'
    }
}

axios.post(crate_endpoint,body,config).then(res => console.log(res.data)).catch(err => console.log(err));

response:

{
  cols: [],
  duration: 4.590081,
  results: [ { rowcount: 1 }, { rowcount: 1 } ]
}

I suspect a formatting error with your curl statement :confused:

CREATE TABLE webservicetests.webperformance (
  metric_name TEXT,
  timestamp timestamp,
  metric_value double precision,
  check text,
  error text,
  error_code integer,
  expected_response text,
  "group" text
  );
13

Hello @proddata

I created the same table as you did above and from a remote system I tried to insert records. I am sending you the following three attempts to demonstrate where it breaks. Please note that the first attempt works, the other two fails:

[root@melbourne-raptor1 results]# curl -sS -H 'Content-Type: application/json' -X POST 'UUU:PPP@209.44.XX.XX:4200/_sql?error_trace=true' -d@- <<- EOF
> {"stmt": "INSERT INTO webservicetests.emre (metric_name,\"timestamp\",metric_value,check,error) VALUES (?,?,?,?,?)","bulk_args":
> [["vus",1618232816000,1.000000,"",""]]
> }
> EOF
{"cols":[],"duration":6.542104,"results":[{"rowcount":1}]}[root@melbourne-raptor1 results]#

########################

[root@melbourne-raptor1 results]# curl -sS -H 'Content-Type: application/json' -X POST 'UUU:PPP@209.44.XX.XX:4200/_sql?error_trace=true' -d@- <<- EOF
> {"stmt": "INSERT INTO webservicetests.emre (metric_name,\"timestamp\",metric_value,check,error,error_code) VALUES (?,?,?,?,?,?)","bulk_args":
> [["vus",1618232816000,1.000000,"","",]]
> }
> EOF
{"error":{"message":"SQLParseException[Failed to parse source [{\"stmt\": \"INSERT INTO webservicetests.emre (metric_name,\\\"timestamp\\\",metric_value,check,error,error_code) VALUES (?,?,?,?,?,?)\",\"bulk_args\": [[\"vus\",1618232816000,1.000000,\"\",\"\",]]}]]","code":4000},"error_trace":"io.crate.exceptions.SQLParseException: Failed to parse source [{\"stmt\": \"INSERT INTO webservicetests.emre (metric_name,\\\"timestamp\\\",metric_value,check,error,error_code) VALUES (?,?,?,?,?,?)\",\"bulk_args\": [[\"vus\",1618232816000,1.000000,\"\",\"\",]]}]\n\tat io.crate.action.sql.parser.SQLRequestParser.parseSource(SQLRequestParser.java:87)\n\tat io.crate.rest.action.SqlHttpHandler.handleSQLRequest(SqlHttpHandler.java:194)\n\tat io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:115)\n\tat io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:79)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.crate.protocols.http.HttpBlobHandler.channelRead0(HttpBlobHandler.java:166)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.crate.auth.HttpAuthUpstreamHandler.handleHttpChunk(HttpAuthUpstreamHandler.java:136)\n\tat io.crate.auth.HttpAuthUpstreamHandler.channelRead0(HttpAuthUpstreamHandler.java:85)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)\n\tat io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)\n\tat io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795)\n\tat io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:480)\n\tat io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378)\n\tat io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)\n\tat io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)\n\tat java.base/java.lang.Thread.run(Thread.java:831)\nCaused by: com.fasterxml.jackson.core.JsonParseException: Unexpected character (']' (code 93)): expected a value\n at [Source: (org.elasticsearch.transport.netty4.ByteBufStreamInput); line: 1, column: 181]\n\tat com.fasterxml.jackson.core.JsonParser._constructError(JsonParser.java:1851)\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportError(ParserMinimalBase.java:707)\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportUnexpectedChar(ParserMinimalBase.java:632)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser._handleUnexpectedValue(UTF8StreamJsonParser.java:2650)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser._nextTokenNotInObject(UTF8StreamJsonParser.java:865)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser.nextToken(UTF8StreamJsonParser.java:757)\n\tat org.elasticsearch.common.xcontent.json.JsonXContentParser.nextToken(JsonXContentParser.java:53)\n\tat io.crate.action.sql.parser.SQLArgsParseElement.parseSubArray(SQLArgsParseElement.java:43)\n\tat io.crate.action.sql.parser.SQLBulkArgsParseElement.parseSubArrays(SQLBulkArgsParseElement.java:47)\n\tat io.crate.action.sql.parser.SQLBulkArgsParseElement.parse(SQLBulkArgsParseElement.java:39)\n\tat io.crate.action.sql.parser.SQLRequestParser.parse(SQLRequestParser.java:105)\n\tat io.crate.action.sql.parser.SQLRequestParser.parseSource(SQLRequestParser.java:77)\n\t... 45 more\n"}

########################

[root@melbourne-raptor1 results]# curl -sS -H 'Content-Type: application/json' -X POST 'UUU:PPP@209.44.XX.XX:4200/_sql?error_trace=true' -d@- <<- EOF
> {"stmt": "INSERT INTO webservicetests.emre (metric_name,\"timestamp\",metric_value,check,error,error_code,expected_response) VALUES (?,?,?,?,?,?,?)","bulk_args":
> [["vus",1618232816000,1.000000,"","",,"test"]]
> }
> EOF
{"error":{"message":"SQLParseException[Failed to parse source [{\"stmt\": \"INSERT INTO webservicetests.emre (metric_name,\\\"timestamp\\\",metric_value,check,error,error_code,expected_response) VALUES (?,?,?,?,?,?,?)\",\"bulk_args\": [[\"vus\",1618232816000,1.000000,\"\",\"\",,\"test\"]]}]]","code":4000},"error_trace":"io.crate.exceptions.SQLParseException: Failed to parse source [{\"stmt\": \"INSERT INTO webservicetests.emre (metric_name,\\\"timestamp\\\",metric_value,check,error,error_code,expected_response) VALUES (?,?,?,?,?,?,?)\",\"bulk_args\": [[\"vus\",1618232816000,1.000000,\"\",\"\",,\"test\"]]}]\n\tat io.crate.action.sql.parser.SQLRequestParser.parseSource(SQLRequestParser.java:87)\n\tat io.crate.rest.action.SqlHttpHandler.handleSQLRequest(SqlHttpHandler.java:194)\n\tat io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:115)\n\tat io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:79)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.crate.protocols.http.HttpBlobHandler.channelRead0(HttpBlobHandler.java:166)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.crate.auth.HttpAuthUpstreamHandler.handleHttpChunk(HttpAuthUpstreamHandler.java:136)\n\tat io.crate.auth.HttpAuthUpstreamHandler.channelRead0(HttpAuthUpstreamHandler.java:85)\n\tat io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)\n\tat io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)\n\tat io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)\n\tat io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)\n\tat io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795)\n\tat io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:480)\n\tat io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378)\n\tat io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)\n\tat io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)\n\tat java.base/java.lang.Thread.run(Thread.java:831)\nCaused by: com.fasterxml.jackson.core.JsonParseException: Unexpected character (',' (code 44)): expected a value\n at [Source: (org.elasticsearch.transport.netty4.ByteBufStreamInput); line: 1, column: 201]\n\tat com.fasterxml.jackson.core.JsonParser._constructError(JsonParser.java:1851)\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportError(ParserMinimalBase.java:707)\n\tat com.fasterxml.jackson.core.base.ParserMinimalBase._reportUnexpectedChar(ParserMinimalBase.java:632)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser._handleUnexpectedValue(UTF8StreamJsonParser.java:2650)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser._nextTokenNotInObject(UTF8StreamJsonParser.java:865)\n\tat com.fasterxml.jackson.core.json.UTF8StreamJsonParser.nextToken(UTF8StreamJsonParser.java:757)\n\tat org.elasticsearch.common.xcontent.json.JsonXContentParser.nextToken(JsonXContentParser.java:53)\n\tat io.crate.action.sql.parser.SQLArgsParseElement.parseSubArray(SQLArgsParseElement.java:43)\n\tat io.crate.action.sql.parser.SQLBulkArgsParseElement.parseSubArrays(SQLBulkArgsParseElement.java:47)\n\tat io.crate.action.sql.parser.SQLBulkArgsParseElement.parse(SQLBulkArgsParseElement.java:39)\n\tat io.crate.action.sql.parser.SQLRequestParser.parse(SQLRequestParser.java:105)\n\tat io.crate.action.sql.parser.SQLRequestParser.parseSource(SQLRequestParser.java:77)\n\t... 45 more\n"}
14

seems like with curl you need to specify null-values with null i.e.

curl -sS -H 'Content-Type: application/json' -X POST 'https://xxxx.cratedb.net:4200/_sql' \
-d '{"stmt":"INSERT INTO webservicetests.webperformance (metric_name,\"timestamp\",metric_value,check,error,error_code,expected_response,\"group\") VALUES (?,?,?,?,?,?,?,?)","bulk_args":[["vus",1618232816000,1.000000,"","",null,"",""]]}'

{"cols":[],"duration":5.260888,"results":[{"rowcount":1}]}

edit: you might also want to do this for empty text values instead of ""

1 Like
15

That was it… Everything works with the http endpoint setup now… My “Select *” command under Admin UI also works.

The original reason for the discussion was psql :slight_smile: By the way, I gave my user full rights to pg_catalog but I was still getting permission complaints from the system when I tried to insert something from a CSV file using psql client remotely. It is ok, I will proceed with http endpoint. Thanks @proddata

1 Like
16

Could you share the permissions that you gave to the user.
I want to replicate the psql issue. We/I often use psql together with CrateDB :thinking:

17

@asavran FYI: select * from sys.privileges where grantee = '<username>' limit 100; returns all privileges for the specified user

18

privileges
privileges1825×773 24.9 KB