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?