https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# general
s
This message was deleted.
k
Hi Abdel - SQL inserts are new, and may not be available in PyDruid yet
✅ 1
a
Thanks for your answer @Kyle ! I have tried to use the API directly with the following query:
Copy code
INSERT INTO d1_ingest VALUES ('2019/08/30T00:00:00' , 'st0' , 0.086852, 0.256154, 0.353368, 0.264800, 0.340716, 0.888801, 0.098555, 0.990292, 0.006500, 0.197772, 0.056110, 0.711823, 0.113245, 0.203696, 0.749044, 0.637718, 0.197120, 0.396791, 0.868563, 0.503080, 0.403444, 0.050145, 0.434476, 0.649876, 0.001515, 0.970305, 0.473175, 0.563040, 0.310380, 0.273118, 0.551110, 0.308798, 0.783165, 0.950974, 0.926163, 0.802391, 0.226309, 0.432790, 0.114836, 0.347638, 0.432519, 0.838368, 0.638415, 0.944477, 0.735955, 0.419511, 0.152170, 0.333158, 0.566972, 0.028693, 0.491042, 0.604122, 0.764282, 0.577141, 0.267561, 0.432286, 0.157174, 0.851707, 0.213233, 0.095803, 0.982957, 0.841460, 0.533001, 0.090531, 0.386728, 0.363502, 0.281768, 0.431700, 0.852836, 0.398835, 0.760999, 0.160066, 0.475869, 0.028385, 0.111324, 0.948984, 0.575755, 0.741031, 0.625195, 0.057176, 0.590370, 0.498445, 0.879716, 0.717900, 0.597301, 0.596217, 0.407540, 0.750143, 0.736843, 0.264539, 0.457388, 0.054152, 0.486477, 0.525835, 0.204051, 0.694023, 0.558147, 0.668027, 0.166661, 0.745691) PARTITIONED BY DAY
But this gives an error too:
Copy code
Error: Plan validation failed
sqlOuterLimit cannot be provided with INSERT.
org.apache.calcite.tools.ValidationException
Any ideas of why is that? Any ideas on how to perform small continuous remote inserts? Thanks!
k
I think this would look more like:
Copy code
REPLACE INTO "d1_ingest" OVERWRITE ALL
WITH "ext" AS (SELECT *
FROM TABLE(
  EXTERN(
    '{"type":"inline","data":"''2019/08/30T00:00:00'' , ''st0'' , 0.086852, 0.256154, 0.353368, 0.264800, 0.340716, 0.888801, 0.098555, 0.990292, 0.006500, 0.197772, 0.056110, 0.711823, 0.113245, 0.203696, 0.749044, 0.637718, 0.197120, 0.396791, 0.868563, 0.503080, 0.403444, 0.050145, 0.434476, 0.649876, 0.001515, 0.970305, 0.473175, 0.563040, 0.310380, 0.273118, 0.551110, 0.308798, 0.783165, 0.950974, 0.926163, 0.802391, 0.226309, 0.432790, 0.114836, 0.347638, 0.432519, 0.838368, 0.638415, 0.944477, 0.735955, 0.419511, 0.152170, 0.333158, 0.566972, 0.028693, 0.491042, 0.604122, 0.764282, 0.577141, 0.267561, 0.432286, 0.157174, 0.851707, 0.213233, 0.095803, 0.982957, 0.841460, 0.533001, 0.090531, 0.386728, 0.363502, 0.281768, 0.431700, 0.852836, 0.398835, 0.760999, 0.160066, 0.475869, 0.028385, 0.111324, 0.948984, 0.575755, 0.741031, 0.625195, 0.057176, 0.590370, 0.498445, 0.879716, 0.717900, 0.597301, 0.596217, 0.407540, 0.750143, 0.736843, 0.264539, 0.457388, 0.054152, 0.486477, 0.525835, 0.204051, 0.694023, 0.558147, 0.668027, 0.166661, 0.745691"}',
    '{"type":"csv","findColumnsFromHeader":false,"columns":["column001","column002","column003","column004","column005","column006","column007","column008","column009","column010","column011","column012","column013","column014","column015","column016","column017","column018","column019","column020","column021","column022","column023","column024","column025","column026","column027","column028","column029","column030","column031","column032","column033","column034","column035","column036","column037","column038","column039","column040","column041","column042","column043","column044","column045","column046","column047","column048","column049","column050","column051","column052","column053","column054","column055","column056","column057","column058","column059","column060","column061","column062","column063","column064","column065","column066","column067","column068","column069","column070","column071","column072","column073","column074","column075","column076","column077","column078","column079","column080","column081","column082","column083","column084","column085","column086","column087","column088","column089","column090","column091","column092","column093","column094","column095","column096","column097","column098","column099","column100","column101","column102"]}',
    '[{"name":"column001","type":"string"},{"name":"column002","type":"string"},{"name":"column003","type":"double"},{"name":"column004","type":"double"},{"name":"column005","type":"double"},{"name":"column006","type":"double"},{"name":"column007","type":"double"},{"name":"column008","type":"double"},{"name":"column009","type":"double"},{"name":"column010","type":"double"},{"name":"column011","type":"double"},{"name":"column012","type":"double"},{"name":"column013","type":"double"},{"name":"column014","type":"double"},{"name":"column015","type":"double"},{"name":"column016","type":"double"},{"name":"column017","type":"double"},{"name":"column018","type":"double"},{"name":"column019","type":"double"},{"name":"column020","type":"double"},{"name":"column021","type":"double"},{"name":"column022","type":"double"},{"name":"column023","type":"double"},{"name":"column024","type":"double"},{"name":"column025","type":"double"},{"name":"column026","type":"double"},{"name":"column027","type":"double"},{"name":"column028","type":"double"},{"name":"column029","type":"double"},{"name":"column030","type":"double"},{"name":"column031","type":"double"},{"name":"column032","type":"double"},{"name":"column033","type":"double"},{"name":"column034","type":"double"},{"name":"column035","type":"double"},{"name":"column036","type":"double"},{"name":"column037","type":"double"},{"name":"column038","type":"double"},{"name":"column039","type":"double"},{"name":"column040","type":"double"},{"name":"column041","type":"double"},{"name":"column042","type":"double"},{"name":"column043","type":"double"},{"name":"column044","type":"double"},{"name":"column045","type":"double"},{"name":"column046","type":"double"},{"name":"column047","type":"double"},{"name":"column048","type":"double"},{"name":"column049","type":"double"},{"name":"column050","type":"double"},{"name":"column051","type":"double"},{"name":"column052","type":"double"},{"name":"column053","type":"double"},{"name":"column054","type":"double"},{"name":"column055","type":"double"},{"name":"column056","type":"double"},{"name":"column057","type":"double"},{"name":"column058","type":"double"},{"name":"column059","type":"double"},{"name":"column060","type":"double"},{"name":"column061","type":"double"},{"name":"column062","type":"double"},{"name":"column063","type":"double"},{"name":"column064","type":"double"},{"name":"column065","type":"double"},{"name":"column066","type":"double"},{"name":"column067","type":"double"},{"name":"column068","type":"double"},{"name":"column069","type":"double"},{"name":"column070","type":"double"},{"name":"column071","type":"double"},{"name":"column072","type":"double"},{"name":"column073","type":"double"},{"name":"column074","type":"double"},{"name":"column075","type":"double"},{"name":"column076","type":"double"},{"name":"column077","type":"double"},{"name":"column078","type":"double"},{"name":"column079","type":"double"},{"name":"column080","type":"double"},{"name":"column081","type":"double"},{"name":"column082","type":"double"},{"name":"column083","type":"double"},{"name":"column084","type":"double"},{"name":"column085","type":"double"},{"name":"column086","type":"double"},{"name":"column087","type":"double"},{"name":"column088","type":"double"},{"name":"column089","type":"double"},{"name":"column090","type":"double"},{"name":"column091","type":"double"},{"name":"column092","type":"double"},{"name":"column093","type":"double"},{"name":"column094","type":"double"},{"name":"column095","type":"double"},{"name":"column096","type":"double"},{"name":"column097","type":"double"},{"name":"column098","type":"double"},{"name":"column099","type":"double"},{"name":"column100","type":"double"},{"name":"column101","type":"double"},{"name":"column102","type":"double"}]'
  )
))
SELECT
  "column001",
  "column002",
  "column003",
  "column004",
  "column005",
  "column006",
  "column007",
  "column008",
  "column009",
  "column010",
  "column011",
  "column012",
  "column013",
  "column014",
  "column015",
  "column016",
  "column017",
  "column018",
  "column019",
  "column020",
  "column021",
  "column022",
  "column023",
  "column024",
  "column025",
  "column026",
  "column027",
  "column028",
  "column029",
  "column030",
  "column031",
  "column032",
  "column033",
  "column034",
  "column035",
  "column036",
  "column037",
  "column038",
  "column039",
  "column040",
  "column041",
  "column042",
  "column043",
  "column044",
  "column045",
  "column046",
  "column047",
  "column048",
  "column049",
  "column050",
  "column051",
  "column052",
  "column053",
  "column054",
  "column055",
  "column056",
  "column057",
  "column058",
  "column059",
  "column060",
  "column061",
  "column062",
  "column063",
  "column064",
  "column065",
  "column066",
  "column067",
  "column068",
  "column069",
  "column070",
  "column071",
  "column072",
  "column073",
  "column074",
  "column075",
  "column076",
  "column077",
  "column078",
  "column079",
  "column080",
  "column081",
  "column082",
  "column083",
  "column084",
  "column085",
  "column086",
  "column087",
  "column088",
  "column089",
  "column090",
  "column091",
  "column092",
  "column093",
  "column094",
  "column095",
  "column096",
  "column097",
  "column098",
  "column099",
  "column100",
  "column101",
  "column102"
FROM "ext"
PARTITIONED BY ALL
a
That worked, thanks @Kyle Hoondert!! However, inserting this one row took 11 seconds. I am looking to do dynamic frequent insertions (as small batches). Is there a better version of this query to allow faster insertions? Thanks a lot!
Here are the insertion execution details:
k
Any opportunity for Kafka/Kinesis?
a
Not really, my use-case involves raw insertions (ideally but not necessarily in SQL) without additional tools. I basically need to send small collected data to a Druid server with a fixed rate (e.g. 100 rows/sec)
k
for something small like this, it might make sense to use the old-fashioned ingestion spec. This would reduce the need to start a controller and worker task and might be quicker because of this
a
I see! To my understanding, ingestion spec requires storing the data to a file and running the ingestion spec while referring to the data file. I basically need to push new data from the memory to Druid from a client machine on a second basis. This is why Pydruid with the INSERT INTO query was my first move. Do you think ingestion spec is still usable for my use-case?
k
yes - you can do it inline
inline_native.json
a
Got it! I just don’t get how would I then run the ingestion spec remotely from the client without copying the data into the Druid machine?
r
if u don't want add kafka to the stack, you can try using redpanda, it should work 100row/s is a too few to batch, I think the overhead of a task for each second is too much
✅ 1
v
The INSERT and REPLACE functionality is only available via the task based SQL API. The task based SQL API is located on the endpoint 
/druid/v2/sql/task
(vs the native based SQL API that is on 
/druid/v2/sql/
). In the console you can select which engine (API) you use from this drop down:
✅ 1
The task based API (https://druid.apache.org/docs/latest/multi-stage-query/api.html) is async, so you issue the query, get a taskId back and then have to monitor the status of that task. The console does it for you
✅ 1
note also that the console by default has an auto engine selector mode that will automatically pick what engine to use for your query (based on the contents of the query), but you can also explicitly select one
It is not recommended to load 1 or only a small number of rows into Druid using batch ingestion (INSERT SQL or 
index_parallel
). This is exactly what streaming ingestion is for so set up a Kafka topic, make a Supervisor that will connect to that topic and then publish your events to that topic one at a time. Batch ingestion in Druid is built with the idea of loading a large amount of data all at once.
✅ 1
a
Thanks @Kyle Hoondert, @Renato Santos and @Vadim!!
Open in Slack
PreviousNext
Powered by