systemdeveloper868/druid repository overview

                        ____             _     _ 
                       |  _ \ _ __ _   _(_) __| |
                       | | | | '__| | | | |/ _` |
                       | |_| | |  | |_| | | (_| |
                       |____/|_|   \__,_|_|\__,_|
                                                 

victor arbiol 2023 -> druid 5.19.0-46-generic 172.17.0.2

docker run -p 88888:8888 systemdeveloper868/druid

(base) root@87eb225eb2f5:/app# druid <--/

/apache-druid-27.0.0/start-druid

http://localhost:8888/⁠

http://172.17.0.3:8888/unified-console.html⁠

https://github.com/apache/druid⁠

Datos de prueba: https://github.com/apache/druid/blob/master/examples/quickstart/tutorial/wikiticker-2015-09-12-sampled.json.gz⁠

⁠Query:

SELECT * FROM "Downloads"

SELECT "countryName", //SUM(added) FILTER(WHERE channel = '#en.wikipedia'), REPLACE(countryName, 'Argentina', 'Madrid')

FROM "wikiticker-2015-09-12-sampled"

GROUP BY "countryName"

limit 5

SELECT "TABLE_CATALOG", COUNT(*) AS "Count" FROM "INFORMATION_SCHEMA"."COLUMNS" GROUP BY 1 ORDER BY 2 DESC

SELECT channel, COUNT() FROM "Downloads" GROUP BY channel ORDER BY COUNT() DESC

SELECT "countryName", SUM(added)

FROM "wikipedia" WHERE "countryName" != 'Angola' GROUP BY countryName

LIMIT 5

SELECT "countryName", SUM(added)

FROM "wikipedia" WHERE "channel" = '#en.wikipedia' AND "countryName" != 'Angola' GROUP BY countryName

LIMIT 5

SELECT REPLACE(countryName, 'United States', 'El ') AS countryName

FROM wikipedia WHERE LENGTH(countryName) > 10

SELECT COUNT(*) AS num_nulls

FROM wikipedia WHERE countryName IS NULL

SELECT countryName, COUNT(*) AS num_occurrences

FROM wikipedia GROUP BY countryName

SELECT countryName, COUNT(DISTINCT countryName) AS num_occurrences

FROM wikipedia GROUP BY countryName

SELECT countryName FROM wikipedia WHERE countryName IS NOT NULL GROUP BY countryName

SELECT LOWER(countryName) AS countryName FROM wikipedia WHERE countryName IS NOT NULL GROUP BY countryName

SELECT REGEXP_REPLACE(countryName, '^Ar', '') AS countryName

FROM wikipedia

SELECT

TIME_EXTRACT(__time, 'DAY') AS dt, TIME_EXTRACT(__time, 'MONTH') AS mn, TIME_EXTRACT(__time, 'YEAR') AS yr, COUNT(1) AS cnt

FROM "invoicing.invoice.v1.created" GROUP BY TIME_EXTRACT(__time, 'DAY'), TIME_EXTRACT(__time, 'MONTH'), TIME_EXTRACT(__time, 'YEAR')

select "signature", JSON_VALUE("controllerExtension", '$.email') AS email from "inline_data"

SELECT JSON_VALUE("invoices", '$[0].taxesOutputs[0].taxableBase.totalAmount') AS totalAmount FROM "inline_data" LIMIT 10

SELECT JSON_VALUE("controllerExtension", '$.email') AS invoice_number, COUNT(*) AS "Count" FROM "inline_data" GROUP BY 1 -- Agrupar por 'invoice_number' ORDER BY 2 DESC -- Ordenar por el recuento en orden descendente

SELECT JSON_VALUE("parties", '$.buyer.taxIdentification.taxIdentificationNumber') AS totalAmount FROM "inline_data" LIMIT 10

SELECT JSON_VALUE("invoices", '$[0].invoiceHeader.invoiceNumber') AS extraeValor FROM "inline_data" WHERE JSON_VALUE("invoices", '$[0].invoiceHeader.invoiceNumber') = '20143201'

SELECT JSON_VALUE("invoices", '$[0].invoiceHeader.invoiceNumber') AS extraeValor FROM "inline_data" WHERE JSON_VALUE("invoices", '$[0].invoiceHeader.invoiceNumber') = '20143201' LIMIT 1

SELECT JSON_VALUE("invoices", '$[0].invoiceHeader.invoiceNumber') AS invoiceNumber, JSON_VALUE("controllerExtension", '$.email') AS email FROM "inline_data" WHERE JSON_VALUE("invoices", '$[0].invoiceHeader.invoiceNumber') = '20143201' AND JSON_VALUE("controllerExtension", '$.email') IS NOT NULL LIMIT 1

SELECT JSON_VALUE("parties", '$.seller.taxIdentification.taxIdentificationNumber') AS totalAmount FROM "inline_data" LIMIT 10

-- Extrae el valor de 'totalAmount' del JSON

SELECT JSON_VALUE("invoices", '$[0].additionalData.relatedDocuments[0].attachmentDescription') AS totalAmount FROM "inline_data" LIMIT 10

-- Extrae el enlace al archivo PDF SELECT JSON_VALUE("invoices", '$[0].additionalData.relatedDocuments[0].attachmentData') AS pdfLink FROM "inline_data" LIMIT 10

SELECT JSON_VALUE("invoices", '$[0].additionalData.relatedDocuments[0].attachmentData') AS totalAmount FROM "inline_data" LIMIT 10

⁠consulta nativa a druid

{ "queryType": "topN", "dataSource": "wikipedia", "dimension": "language", "threshold": 4, "metric": "edit_count", "granularity": "all", "filter": { "type": "selector", "dimension": "namespace", "value": "article" }, "aggregations": [ { "type": "longSum", "name": "edit_count", "fieldName": "count" } ], "intervals": [ "2013-06-01T00:00/2020-01-01T00" ] }

curl -X 'POST' -H 'Content-Type:application/json' -d @simple_query_native.json http://localhost:8888/druid/v2?pretty⁠

EXPLAIN PLAN FOR SELECT channel, COUNT() FROM wikipedia WHERE channel IN (SELECT page FROM wikipedia GROUP BY page ORDER BY COUNT() DESC LIMIT 10) GROUP BY channel

EXPLAIN PLAN FOR INSERT INTO wikipedia2 SELECT TIME_PARSE("timestamp") AS __time, namespace, cityName, countryName, regionIsoCode, metroCode, countryIsoCode, regionName FROM TABLE( EXTERN( '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz⁠"]}', '{"type":"json"}', '[{"name":"timestamp","type":"string"},{"name":"namespace","type":"string"},{"name":"cityName","type":"string"},{"name":"countryName","type":"string"},{"name":"regionIsoCode","type":"string"},{"name":"metroCode","type":"long"},{"name":"countryIsoCode","type":"string"},{"name":"regionName","type":"string"}]' ) ) PARTITIONED BY ALL

EXPLAIN PLAN FOR REPLACE INTO wikipedia OVERWRITE ALL SELECT TIME_PARSE("timestamp") AS __time, namespace, cityName, countryName, regionIsoCode, metroCode, countryIsoCode, regionName FROM TABLE( EXTERN( '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz⁠"]}', '{"type":"json"}', '[{"name":"timestamp","type":"string"},{"name":"namespace","type":"string"},{"name":"cityName","type":"string"},{"name":"countryName","type":"string"},{"name":"regionIsoCode","type":"string"},{"name":"metroCode","type":"long"},{"name":"countryIsoCode","type":"string"},{"name":"regionName","type":"string"}]' ) ) PARTITIONED BY DAY CLUSTERED BY cityName, countryName

SELECT "countryName", SUM(added)

FROM "wikipedia" WHERE "countryName" != 'Angola' GROUP BY countryName

LIMIT 5

SELECT "countryName", SUM(added)

FROM "wikipedia" WHERE "channel" = '#en.wikipedia' AND "countryName" != 'Angola' GROUP BY countryName

LIMIT 5

SELECT "page", "countryName", COUNT() AS "Edits", COUNT() FILTER (WHERE "cityName" = '') AS "filtered_dist_cityName" FROM "wikipedia"

WHERE "channel" = '#en.wikipedia' AND "countryName" = 'Angola'

GROUP BY 1, 2 ORDER BY "Edits" DESC

SELECT REPLACE(countryName, 'United States', 'El ') AS countryName

FROM wikipedia WHERE LENGTH(countryName) > 10

SELECT COUNT(*) AS num_nulls

FROM wikipedia WHERE countryName IS NULL

SELECT countryName, COUNT(*) AS num_occurrences

FROM wikipedia GROUP BY countryName

SELECT countryName, COUNT(DISTINCT countryName) AS num_occurrences

FROM wikipedia GROUP BY countryName

SELECT countryName FROM wikipedia WHERE countryName IS NOT NULL GROUP BY countryName

SELECT LOWER(countryName) AS countryName FROM wikipedia WHERE countryName IS NOT NULL GROUP BY countryName

SELECT REGEXP_REPLACE(countryName, '^Ar', '') AS countryName

FROM wikipedia

busca solo france

SELECT "countryName", COUNT(*) AS "Count" FROM "wikiticker-2015-09-12-sampled" WHERE "countryName" = 'France' GROUP BY "countryName" ORDER BY "Count" DESC LIMIT 5

Remplaza en otra clumna:

SELECT "countryName", SUM(added) FILTER(WHERE channel = '#en.wikipedia'), REPLACE(countryName, 'Angola', 'Madrid')

FROM "wikiticker-2015-09-12-sampled" GROUP BY "countryName" limit 5

BORRAR O NO MOSTRAR fRANCE

SELECT "countryName", COUNT(*) AS "Count" FROM "wikiticker-2015-09-12-sampled" WHERE "countryName" != 'France' GROUP BY "countryName" ORDER BY "Count" DESC LIMIT 5

SELECT "countryName", SUM(added) FILTER(WHERE channel = '#en.wikipedia') FROM "wikiticker-2015-09-12-sampled" GROUP BY "countryName" limit 5

SELECT "__time", -- en XXX busca YYY muestra como ZZZ JSON_VALUE(invoices, '$[0].taxesOutputs[0].taxableBase.totalAmount') as "Impuestos", JSON_VALUE(invoices, '$[0].invoiceTotals.invoiceTotal') as "Total", JSON_VALUE(parties, '$.seller.taxIdentification.taxIdentificationNumber') as "NIF vendedor", JSON_VALUE(parties, '$.buyer.taxIdentification.taxIdentificationNumber') as "NIF comprador", JSON_VALUE(invoices, '$[0].invoiceHeader.invoiceNumber') as "invoiceNumber", -- los siguientes hay que tratarlos. -- muestra ruta:
JSON_PATHS(invoices) AS "INVOICE", -- Muestra Ramas: "parties" FROM "invoicing.invoice.v1.created"

JSON_VALUE y JSON_QUERY