How to insert data with SQL insert ?

[XavierZ]

Hi all,

I tried to do this, however I ran into error code 405, so I came here looking for help.

For the given data sample, I can ingest it using batch commit with an ingest spec, but I cannot insert it using SQL 'INSERT' statements, resulting in error code 405.

// sample data {"time": "2023-05-02T00:47:00.496Z","cooperative": "ABCBT","factory": "BTS","area": "1","dev": "BP","phaseA": 1.111} // insert SQL generated by Druid REPLACE INTO inline_data OVERWRITE ALL WITH ext AS (SELECT * FROM TABLE( EXTERN( '{"type":"inline","data":"{\"time\": \"2023-05-02T00:47:00.496Z\",\"cooperative\": \"ABCBT\",\"factory\": \"BTS\",\"area\": \"1\",\"dev\": \"BP\",\"phaseA\": 1.111}"}', '{"type":"json"}', '[{"name":"time","type":"string"},{"name":"cooperative","type":"string"},{"name":"factory","type":"string"},{"name":"area","type":"string"},{"name":"dev","type":"string"},{"name":"phaseA","type":"double"}]' ) )) SELECT TIME_PARSE("time") AS __time, cooperative, factory, area, dev, phaseA FROM ext PARTITIONED BY DAY

I cannot even insert a sample from the reference either, encountering the same error 405.

What could be wrong, and how can I resolve this issue correctly? 

Thank you for all your patience and time, I really appreciate your time and patience.

[AR]

Hi,

HTTP  405 is a "Method Not Allowed" response. To execute the insert using SQL, you have to use the MSQ engine. Are you using the MSQ API to execute this query or from the console? If you are using the API, check if you are using the correct HTTP method (POST)?

Ref: https://druid.apache.org/docs/24.0.2/multi-stage-query/api.html

"You submit queries to the MSQ task engine using the POST /druid/v2/sql/task/ endpoint."

If you are already doing this, then check if there is a proxy in front of the API which is not allowing POST requests.

Regards,

AR.

[Xin Zhao]

Hi thank you, and I am using Web-UI from localhost:8888, choosingupload data-paste data(copy&paste the sample)-paser(next)-configrure schema(choose SQL tab, then start loading data).

Is it necessary to set HTTP method with this way?

Regards,

Xavier

AR <arte...@gmail.com> 于2023年8月21日周一 14:03写道：

--
You received this message because you are subscribed to a topic in the Google Groups "Druid User" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/druid-user/GRLHx8eEwtQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to druid-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/04b6341a-43ff-4540-bd29-2f20c1671aaan%40googlegroups.com.

[John Kowtko]

Hi Xavier,

For executing MSQ ingestion statements from the web console, you can just enter them in the Query panel as a SQL query.   I just copy/pasted your original SQL statement above and it works fine for me (screenshot attached).

But as AR mentions you should also be able to submit this same SQL statement via the sql/task API endpoint.

If neither of these work for you, then check to see if you have MSQ enabled on your cluster (second screenshot).  If now, then you may need to add 

"druid-multi-stage-query" to your list of extension.  You should also be running on Druid 25 or later I think.

Let us know if you are able to make any further progress here.

Thanks.  John

[XavierZ]

Hi John,

Thank you very much for your advice. I followed the instructions in your screenshot (as shown in my screenshot), but still failed. However, I discovered that I'm using Druid 24.0. Could this be the reason for my failure?

Additionally, I'm not sure why Druid 24.0 would fail on this SQL feature, since the reference doc   indicates that the druid-multi-stage-query is enabled in Druid 24.0.

Thank you once again, and I look forward to hearing from you.

Xavier

[John Kowtko]

Hi Xavier,

That error suggests the router does not recognize the URL endpoint ... did you add  "druid-multi-stage-query" to your common.runtime.properties file where you are sending the requests? 

(ref https://druid.apache.org/docs/latest/operations/web-console)

Thanks.  John