Webinar: Building a Real-Time Platform for Millions of Smart Meter Events

Register Now
Skip to content
Start free
Start free
Explore

Try CrateDB Live: Industrial IoT

Scenario: Industrial IoT
  • 1. Choose Scenario
  • 2. Get Ready
  • 3. Run CrateDB
  • 4. Import Data
  • 5. Explore Queries
  • 6. More Queries
  • 7. Connect
  • 8. Next Steps

Industrial IoT Dataset

In this step, we load the sample weather dataset into three tables in CrateDB using the COPY FROM statements.

The dataset is provided as JSON files hosted in S3:

  • https://guided-path.s3.us-east-1.amazonaws.com/plants.json
  • https://guided-path.s3.us-east-1.amazonaws.com/devices.json
  • https://guided-path.s3.us-east-1.amazonaws.com/maintenance_log.json
  • https://guided-path.s3.us-east-1.amazonaws.com/iot_demo_dataset.json

CrateDB can ingest data directly from HTTP(S) endpoints, so there is no need to download the file locally.

1. Create the target tables:

1.1 plants

The first table is plants. ERP master data — one row per industrial facility (5 rows)

 
CREATE TABLE IF NOT EXISTS rtia.plants (
    plant_id             TEXT PRIMARY KEY,
    plant_name           TEXT,
    city                 TEXT,
    federal_state        TEXT,
    industry_segment     TEXT,
    employee_count       INTEGER,
    operational_since    INTEGER,
    plant_manager        TEXT,
    annual_revenue_eur_m DOUBLE PRECISION,
    certifications       ARRAY(TEXT),
    geo_location         GEO_POINT
);

1.2 devices

CMMS asset registry — one row per physical device (500 rows) 
CREATE TABLE IF NOT EXISTS rtia.devices (
    device_id               TEXT PRIMARY KEY,
    device_type             TEXT,
    plant_id                TEXT,
    line_id                 TEXT,
    manufacturer            TEXT,
    model                   TEXT,
    serial_number           TEXT,
    installation_date       TIMESTAMP WITH TIME ZONE,
    last_maintenance_date   TIMESTAMP WITH TIME ZONE,
    next_maintenance_due    TIMESTAMP WITH TIME ZONE,
    warranty_expiry         TIMESTAMP WITH TIME ZONE,
    asset_value_eur         DOUBLE PRECISION,
    responsible_technician  TEXT
);

1.3 maintenance_log

Work order history — preventive, corrective, and emergency jobs (~1,700 rows)

 
CREATE TABLE IF NOT EXISTS rtia.maintenance_log (
    work_order_id       TEXT PRIMARY KEY,
    device_id           TEXT,
    plant_id            TEXT,
    maintenance_type    TEXT,              -- 'preventive' | 'corrective' | 'emergency'
    technician          TEXT,
    scheduled_date      TIMESTAMP WITH TIME ZONE,
    completed_date      TIMESTAMP WITH TIME ZONE,
    duration_hours      DOUBLE PRECISION,
    cost_eur            DOUBLE PRECISION,
    status              TEXT,              -- 'completed' | 'scheduled'
    notes               TEXT INDEX USING FULLTEXT WITH (analyzer = 'standard'),
    notes_embedding     FLOAT_VECTOR(384)  -- sentence-transformers/all-MiniLM-L6-v2
);

1.4 iot_data

Sensor readings — 500,000 rows across 500 devices and 5 plants. Telegraf line-protocol shape (hash_id / timestamp / name / tags / fields), so the same table can be fed either by COPY FROM or by Telegraf's outputs.cratedb (postgres/crate) plugin. That plugin can't write a GEO_POINT, so the geo coords ride along as plain doubles in `fields` and geo_location is GENERATED from them. Partitioned by day.

 
CREATE TABLE IF NOT EXISTS rtia.iot_data (
    hash_id      BIGINT,
    "timestamp"  TIMESTAMP WITH TIME ZONE,
    name         TEXT,                          -- measurement name (= "iot_data")
    tags         OBJECT(DYNAMIC),               -- device_id, status, metadata_*, ... (indexed strings)
    fields       OBJECT(DYNAMIC) AS (
        metric_value  DOUBLE PRECISION,
        quality_score DOUBLE PRECISION,
        geo_lon       DOUBLE PRECISION,
        geo_lat       DOUBLE PRECISION
    ),
    day          TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('day', "timestamp"),
    event_week   TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('week', "timestamp"),
    geo_location GEO_POINT GENERATED ALWAYS AS [fields['geo_lon'], fields['geo_lat']],
    PRIMARY KEY (hash_id, "timestamp", event_week)
) PARTITIONED BY (event_week);

1.5 locations

Reference geometries for geo queries (DISTANCE / WITHIN). Lets the advanced queries look a location up by name instead of hand-coding it inline. location_type is 'point' (a coordinate in geo_location, used with DISTANCE) or 'area' (a polygon in geo_area, used with WITHIN).

 
CREATE TABLE IF NOT EXISTS rtia.locations (
    location_name   TEXT PRIMARY KEY,
    location_type   TEXT,              -- 'point' | 'area'
    geo_location    GEO_POINT,         -- set when location_type = 'point'
    geo_area        GEO_SHAPE          -- set when location_type = 'area'
);

1.6 knn_searches

Reference query vectors for the VECTOR SEARCH section (KNN_MATCH). Lets a search look its embedding up by name instead of pasting a 384-element literal inline. Embeddings are FLOAT_VECTOR(384) from sentence-transformers/ all-MiniLM-L6-v2, precomputed by generate_embeddings.py (normalized).

 
CREATE TABLE IF NOT EXISTS rtia.knn_searches (
    query_name      TEXT,
    search_string   TEXT PRIMARY KEY,
    embedding       FLOAT_VECTOR(384)
);

2. Import the data

2.1 Import the data using COPY FROM

Run the following statements to load the data:

 
COPY rtia.plants 
FROM 
  'https://guided-path.s3.us-east-1.amazonaws.com/plants.json' 
WITH 
  (format = 'json');

COPY rtia.devices 
FROM 
  'https://guided-path.s3.us-east-1.amazonaws.com/devices.json' 
WITH 
  (format = 'json');

COPY rtia.maintenance_log 
FROM 
  'https://guided-path.s3.us-east-1.amazonaws.com/maintenance_log.json' 
WITH 
  (format = 'json');

-- This statement can take some time if you use the CrateDB Cloud shared free tier (40-80 seconds)
COPY rtia.iot_data 
FROM 
  'https://guided-path.s3.us-east-1.amazonaws.com/iot_demo_dataset.json' 
WITH 
  (format = 'json');

Note that if you are using a Crate Cloud free tier account, this will take a few dozens of seconds, as we use a shared environment with limited resources. For information on the performance aspect of loading lots of data, see here.

2.2 Insert data using INSERT INTO

 
INSERT INTO rtia.locations (location_name, location_type, geo_location, geo_area) VALUES
    ('Stuttgart', 'point', [9.1819,  48.7843], NULL),   -- PLANT_STUTTGART
    ('Frankfurt', 'point', [8.6638,  50.1071], NULL),   -- Frankfurt (Main) Hauptbahnhof
    ('Munich',    'point', [11.5586, 48.1402], NULL),   -- PLANT_MUNICH
    ('Hamburg',   'point', [10.0068, 53.5528], NULL),   -- PLANT_HAMBURG
    ('Dortmund',  'point', [7.459,   51.5178], NULL),   -- PLANT_DORTMUND
    ('Leipzig',   'point', [12.3815, 51.3454], NULL),   -- PLANT_LEIPZIG
    ('Bavaria',   'area',  NULL, 'POLYGON((10.0 47.3, 13.8 47.3, 13.8 50.6, 10.0 50.6, 10.0 47.3))')
ON CONFLICT (location_name) DO UPDATE SET
    location_type = excluded.location_type,
    geo_location  = excluded.geo_location,
    geo_area      = excluded.geo_area;

INSERT INTO rtia.knn_searches (query_name, search_string, embedding) VALUES
    ('thermal_event', 'emergency shutdown thermal runaway critical breach',
     [-0.011478, 0.043054, 0.068243, 0.064004, 0.095670, 0.000572, -0.017629, 0.009207, 0.037896, 0.065667, -0.022590, -0.024912, -0.002456, 0.010242, 0.073655, 0.001565, 0.007159, -0.029317, -0.038404, 0.017319, -0.058639, 0.003341, -0.063473, 0.072026, -0.047172, -0.012833, 0.093039, 0.027603, -0.089090, 0.066654, 0.012690, -0.078791, -0.029251, 0.050338, 0.106364, 0.045208, -0.027110, 0.013017, -0.029293, -0.050564, 0.011383, 0.017336, -0.012247, 0.011940, 0.024083, 0.049612, -0.026346, -0.045013, 0.029060, 0.007145, 0.011841, 0.023898, -0.024726, 0.043182, 0.013893, 0.027403, 0.020604, -0.069510, 0.017415, 0.052510, 0.044041, -0.004672, -0.013388, -0.019782, 0.094914, 0.007348, 0.050105, -0.010303, 0.022745, 0.075975, 0.035979, -0.008378, 0.000961, -0.038705, 0.059468, 0.059752, -0.005004, -0.065162, 0.000268, -0.016251, 0.029966, -0.040655, -0.029828, -0.041880, -0.080317, 0.008052, 0.088959, -0.051525, 0.020070, -0.040633, -0.000993, -0.006881, 0.100413, 0.033302, 0.161225, -0.042835, 0.019930, -0.085742, 0.019389, -0.037399, 0.012035, -0.042284, -0.030123, 0.010710, 0.028724, 0.018337, -0.008546, -0.061195, 0.019388, -0.087372, -0.012936, 0.007087, 0.050714, -0.087845, 0.103732, -0.013084, -0.047348, 0.046684, -0.007553, 0.050141, 0.014544, 0.006805, -0.134873, -0.018838, -0.075004, 0.007368, 0.012567, 0.000000, 0.066041, 0.021125, -0.053560, 0.074494, 0.074935, -0.020815, -0.047537, -0.037259, 0.020278, 0.043492, 0.018228, -0.014717, 0.030334, 0.003795, -0.006387, -0.013785, 0.024395, 0.039537, -0.134480, 0.024323, -0.037442, -0.008016, 0.067192, 0.003125, 0.081436, 0.034226, -0.018625, 0.026353, -0.010756, 0.020524, -0.041376, -0.019161, -0.021795, 0.070802, -0.078663, 0.015277, -0.023966, -0.025887, -0.037550, -0.041125, 0.005437, -0.017454, -0.052347, 0.088392, -0.012927, -0.041121, -0.064009, 0.008237, 0.026937, -0.058680, -0.054573, -0.001015, -0.075410, 0.044393, -0.079813, 0.001177, 0.060175, -0.014297, 0.106042, 0.017346, -0.025019, 0.059114, 0.003257, 0.004899, 0.067135, 0.041310, 0.044586, -0.030985, 0.007896, 0.002280, -0.033297, 0.049767, 0.029202, -0.125069, -0.028119, -0.029995, 0.050211, 0.032105, 0.049242, -0.024615, -0.049035, -0.094043, 0.120040, 0.027394, -0.014924, -0.015036, -0.031944, 0.012848, -0.098447, -0.011195, -0.052581, -0.096715, 0.082422, 0.079408, -0.015532, -0.000000, -0.024779, -0.038041, 0.008504, -0.004333, -0.005733, -0.023159, -0.090424, 0.080686, -0.039083, 0.050459, 0.006773, -0.051947, -0.044095, -0.079148, -0.023628, -0.018166, 0.106280, -0.062782, 0.011827, 0.053975, 0.007786, -0.020030, -0.068580, -0.047648, 0.003196, 0.069132, -0.027482, 0.050004, -0.065409, 0.021364, -0.066760, -0.001348, 0.000316, 0.092127, -0.006747, -0.020118, 0.032600, -0.010893, 0.036276, -0.087078, 0.068626, 0.079418, 0.024192, 0.007662, -0.060755, 0.009799, 0.012319, -0.019580, -0.008017, 0.063879, -0.032735, -0.101500, -0.025498, 0.124191, -0.030210, -0.019743, -0.009794, 0.001535, -0.062587, -0.045624, 0.080597, 0.069800, 0.095006, 0.084255, 0.006575, 0.002433, -0.024583, -0.051904, 0.052816, -0.080232, 0.005999, 0.062840, 0.057852, -0.068086, 0.007118, -0.027359, -0.058325, -0.071237, -0.017940, 0.044664, 0.004451, -0.010960, -0.099821, 0.018598, 0.147451, -0.059832, -0.011259, 0.065975, 0.061991, -0.022777, -0.038795, -0.057030, 0.057895, 0.022130, -0.019695, -0.000000, 0.024478, 0.042911, 0.013029, 0.064037, 0.089978, -0.082836, -0.003860, 0.016495, -0.015037, 0.021760, -0.032162, -0.013786, 0.042082, -0.004538, -0.065143, -0.079658, -0.023279, 0.049646, -0.020438, -0.041593, -0.019431, -0.004476, 0.124162, 0.020714, -0.004367, 0.093105, -0.016126, -0.089913, 0.001349, 0.023962, -0.167486, -0.018711, 0.003669, 0.024267, -0.009303, 0.143823, 0.107515, 0.013116, 0.041433, -0.066030, -0.000854, -0.076876, 0.045853, 0.029815, -0.019033, -0.069257, -0.087403, 0.025355, 0.001512, -0.011782, 0.021092, 0.002155, -0.013842, 0.071471, -0.024538, -0.054623, -0.071074, 0.030810, 0.021575, 0.019355, 0.021682, -0.059699, -0.083268, -0.059716]),
    ('calibration_drift', 'calibration drift sensor out of range recalibration',
     [-0.051035, -0.012086, 0.009185, 0.011959, 0.030855, -0.032092, 0.047140, -0.033656, -0.020369, -0.036948, 0.007216, -0.087172, 0.020678, -0.015717, -0.058595, -0.007764, -0.041596, 0.075439, 0.091939, -0.001096, -0.061234, 0.077583, 0.019697, 0.073226, -0.005067, -0.047634, -0.060191, -0.019093, -0.056894, -0.044473, -0.016874, 0.030721, -0.112320, -0.036791, 0.038198, 0.026981, 0.025311, 0.025826, 0.000570, 0.041132, 0.064648, 0.056142, 0.063514, 0.067234, 0.057484, 0.066036, 0.088904, 0.011302, 0.008791, -0.006081, -0.023096, 0.075909, -0.011204, -0.021668, -0.036911, 0.014936, 0.055026, 0.070394, 0.027417, -0.073898, 0.047590, 0.012294, -0.032198, -0.046961, -0.087017, -0.034823, -0.025591, 0.018953, 0.036982, 0.007687, -0.026355, 0.077735, 0.048943, -0.066389, 0.061225, -0.021212, -0.019837, 0.037563, 0.106218, -0.014727, -0.022673, -0.040567, -0.051226, -0.024742, -0.002614, -0.017668, 0.039564, 0.013332, 0.079627, -0.050703, 0.047466, -0.045351, -0.088096, 0.032007, -0.012221, -0.025654, 0.051231, 0.021430, 0.099923, -0.013556, -0.043484, -0.017430, -0.051671, 0.042399, -0.006889, 0.005985, 0.019179, 0.032890, -0.015307, 0.073639, 0.034323, -0.018299, 0.005302, 0.050716, 0.010630, 0.058018, -0.084820, 0.044095, -0.055705, -0.005905, -0.137647, 0.031593, -0.046264, 0.067344, 0.028523, 0.064792, 0.076817, 0.000000, -0.025456, -0.016292, 0.019023, 0.035571, -0.027849, 0.004989, -0.097542, 0.036811, 0.009921, 0.042414, 0.017103, 0.041842, -0.052060, -0.013117, 0.036764, -0.044680, -0.013653, -0.070039, -0.030077, 0.041153, 0.010143, -0.050808, -0.056760, -0.016733, -0.068122, 0.103044, -0.010818, 0.181244, -0.051268, -0.016217, 0.029685, 0.007431, 0.009555, -0.069852, 0.064253, -0.073860, 0.017746, 0.061945, -0.051743, 0.022013, 0.020716, 0.085884, 0.026445, 0.016295, 0.102763, -0.060863, -0.009912, 0.020031, 0.043148, 0.114817, -0.032892, 0.005751, -0.019334, -0.009304, -0.001495, -0.046156, 0.036487, -0.030976, -0.009029, 0.000476, 0.002381, -0.010916, 0.042387, -0.057385, 0.082523, 0.043992, -0.019240, 0.041294, 0.000775, 0.030906, 0.019232, -0.065465, -0.008269, 0.040838, -0.022915, -0.046005, 0.011223, 0.080624, 0.070919, -0.113186, 0.004643, 0.069218, 0.014963, -0.008849, -0.019047, -0.061411, -0.091824, -0.009941, -0.045416, 0.066454, 0.001043, 0.020522, -0.048354, 0.012729, -0.026494, -0.000000, 0.001383, 0.050811, 0.036909, -0.002596, -0.052417, -0.012330, 0.072417, 0.130301, 0.073259, 0.033117, -0.070273, -0.126579, -0.026891, 0.120007, -0.001667, 0.018481, 0.039572, -0.039833, 0.024304, -0.036124, 0.075377, 0.019513, -0.042496, -0.059820, 0.054342, 0.057946, -0.066823, 0.079298, -0.007513, -0.116884, -0.040101, -0.014504, 0.028860, -0.080016, 0.003965, -0.053900, -0.061927, -0.019288, 0.010171, 0.075817, 0.010347, 0.040745, 0.009464, -0.020741, 0.047049, 0.025990, 0.063010, 0.011812, -0.006103, 0.008887, -0.020569, -0.012878, -0.051594, -0.013850, -0.046345, 0.092972, -0.006409, 0.018904, -0.050690, -0.027731, 0.058577, -0.051388, 0.029732, 0.014545, 0.072327, 0.073164, 0.061165, 0.001365, 0.010104, 0.056759, 0.098508, -0.018889, 0.037506, -0.062217, -0.066294, -0.129059, -0.069605, -0.029877, 0.038734, -0.066487, -0.029685, -0.056709, 0.005697, 0.056557, 0.029152, -0.036330, -0.078956, -0.113411, 0.053932, 0.007737, 0.051734, -0.080286, -0.009784, 0.007312, -0.046951, -0.000000, -0.105385, 0.099243, -0.105156, 0.070887, -0.011483, 0.010448, -0.007031, -0.024479, 0.022246, -0.056483, 0.018004, -0.029958, 0.039605, 0.087798, 0.024705, -0.022557, 0.027780, -0.033418, -0.032622, 0.043866, 0.019526, 0.033714, -0.020691, -0.026146, -0.002554, 0.009873, -0.055779, 0.070545, 0.053840, -0.052702, -0.004847, -0.030299, 0.060125, 0.013150, -0.044484, 0.072203, -0.060315, 0.099043, 0.034120, -0.017038, -0.063474, 0.029474, -0.057049, 0.059251, -0.014857, -0.021676, 0.027327, 0.041868, -0.000480, -0.003080, 0.043631, 0.033142, -0.034117, -0.059176, -0.017695, -0.081661, -0.033873, -0.070498, -0.066263, 0.022925, -0.133360, -0.027028, -0.124666, -0.044015]),
    ('mechanical_failure', 'worn parts replaced mechanical failure vibration',
     [-0.044131, 0.002601, 0.119617, 0.002723, -0.019353, -0.013911, 0.006483, -0.010646, -0.060671, -0.058114, 0.005995, 0.010690, 0.038229, -0.013541, 0.007073, 0.007725, 0.089777, 0.049640, -0.058326, -0.031149, -0.066523, 0.081250, -0.061660, 0.037842, -0.024767, 0.075559, -0.034891, 0.046186, 0.037332, -0.041837, -0.007793, 0.014610, -0.064326, 0.044034, 0.018822, -0.009541, -0.015713, 0.010730, -0.034766, -0.091389, -0.046320, 0.057307, 0.003318, -0.033590, 0.050668, 0.068298, 0.048570, -0.070472, 0.071937, 0.067564, -0.016040, -0.021986, 0.177825, 0.003352, 0.028135, 0.042933, 0.064887, -0.032161, -0.046746, -0.000628, 0.143058, -0.008943, -0.008639, 0.009141, -0.003376, -0.016323, 0.048181, -0.102780, 0.018031, 0.061060, -0.041043, -0.050914, -0.030137, 0.103578, -0.036875, 0.054605, 0.000997, -0.028035, -0.019141, 0.026532, -0.036117, -0.056124, 0.051076, -0.039631, 0.056639, 0.023025, 0.066762, -0.026307, -0.000344, 0.021540, -0.027653, 0.053067, 0.060766, 0.021175, 0.059377, 0.078612, 0.003319, 0.058167, 0.080692, 0.048153, -0.044742, 0.002025, 0.112197, 0.055121, -0.054438, 0.001055, -0.075997, 0.045972, 0.031197, 0.038498, -0.011510, -0.003527, -0.011242, -0.005668, -0.011098, -0.086370, -0.078488, 0.026599, -0.042065, 0.039184, 0.086522, -0.058146, -0.019621, -0.054432, -0.000904, -0.081623, -0.007177, 0.000000, 0.010606, -0.025595, 0.022558, -0.060717, -0.018611, -0.019412, 0.000182, -0.022938, 0.055184, -0.071466, -0.011515, 0.025430, 0.019427, -0.131553, -0.017389, -0.023379, 0.044359, -0.089202, -0.027585, -0.020736, -0.033035, 0.040750, 0.053771, 0.037444, 0.005426, 0.049289, 0.094991, -0.009100, -0.024526, 0.001404, 0.047318, 0.027970, -0.016882, -0.010624, -0.024772, -0.055578, 0.059921, 0.021684, -0.028540, -0.037149, -0.010240, 0.016297, -0.010156, 0.021041, -0.025347, -0.021100, 0.033741, 0.083391, -0.001420, -0.047312, -0.024273, 0.018472, 0.027630, -0.003073, -0.083001, 0.008116, 0.050816, -0.063449, -0.049313, -0.034210, -0.016600, 0.031180, 0.125903, -0.027879, 0.082319, -0.002635, 0.042233, -0.020714, -0.023348, -0.049759, 0.008751, -0.031549, -0.028925, 0.075056, -0.031037, -0.007713, -0.071598, 0.011730, -0.061106, -0.055055, -0.027788, 0.023565, -0.035126, 0.035880, 0.064375, 0.024506, 0.047212, -0.074899, -0.023751, 0.049294, 0.002693, -0.021530, -0.023373, 0.032039, 0.032685, -0.000000, -0.014415, 0.012423, 0.060664, 0.084677, 0.018857, -0.009315, -0.041209, 0.044806, -0.077971, 0.075724, 0.063163, -0.073009, -0.167888, 0.026029, -0.089966, 0.055452, -0.072475, 0.018659, 0.028575, -0.033217, 0.087572, 0.083609, -0.036834, 0.020805, -0.036535, 0.042862, -0.032830, -0.035207, -0.051409, -0.056980, 0.017928, -0.025341, 0.020628, 0.058508, -0.012553, 0.071844, -0.033899, 0.042945, 0.027168, -0.058627, 0.059153, 0.010775, 0.063171, 0.025458, -0.030392, -0.140455, -0.040269, -0.048869, -0.026084, 0.024963, 0.124098, -0.013846, 0.041024, -0.021173, 0.039856, 0.084910, 0.009870, -0.038900, -0.024330, 0.019982, 0.001071, 0.019496, 0.070563, 0.028942, 0.054641, 0.047368, 0.034875, 0.004193, 0.007896, -0.001621, 0.049093, 0.023944, 0.045952, -0.047391, 0.048641, 0.007988, -0.079411, 0.037224, -0.046588, 0.033530, 0.042729, -0.126141, 0.049233, 0.046929, -0.014687, 0.005890, 0.006465, 0.039800, -0.008725, -0.061868, 0.033047, 0.094837, 0.007147, -0.020884, 0.046960, -0.000000, -0.039862, 0.005808, -0.089148, -0.081639, 0.038853, -0.027630, 0.030217, 0.002394, -0.030385, 0.012397, -0.050438, -0.093475, 0.106725, 0.018561, -0.098180, 0.031973, -0.057286, 0.119402, -0.077719, -0.061192, -0.051572, 0.000694, 0.040006, 0.049681, -0.012520, -0.042708, -0.085159, -0.012214, -0.011113, 0.090318, -0.046134, -0.013717, 0.021460, -0.105778, 0.016173, -0.084242, -0.056988, -0.104604, -0.029067, -0.027766, -0.029407, 0.024790, -0.019288, 0.076814, 0.080156, -0.112865, 0.007033, -0.030252, -0.060165, 0.043647, 0.004977, 0.053925, -0.027156, 0.024144, -0.107645, -0.016577, 0.029849, 0.057755, 0.003543, -0.005250, -0.038524, -0.106235, 0.063819, -0.015962]),
    ('signal_cable', 'signal cable damaged transmission restored',
     [-0.086230, 0.024834, 0.109041, 0.046749, 0.014782, 0.030749, -0.072480, 0.008217, -0.092614, -0.001928, 0.059583, 0.050382, -0.012353, 0.011198, -0.045446, -0.024772, 0.042055, 0.004489, -0.018415, -0.013590, -0.049501, 0.031462, -0.023657, 0.004143, 0.084482, -0.017293, 0.038422, 0.041009, 0.050046, -0.089747, -0.077151, -0.006199, -0.021987, 0.044431, -0.064455, 0.082827, 0.060869, -0.065668, 0.030819, -0.050995, -0.001428, -0.003368, -0.114463, -0.013800, -0.001306, -0.022554, 0.115227, -0.020921, 0.040754, -0.018766, -0.003837, 0.087541, -0.050241, 0.071729, 0.061985, -0.007662, -0.053490, 0.082524, 0.022905, 0.004327, 0.052037, 0.023866, 0.015641, -0.009906, 0.011914, 0.010752, 0.023453, 0.009325, 0.055967, 0.002318, 0.006074, -0.059076, 0.001248, 0.043852, 0.031750, 0.059050, -0.017959, 0.050380, -0.039222, 0.073519, -0.070503, -0.055321, -0.043837, 0.050724, 0.008717, 0.041479, -0.018480, -0.017143, 0.010702, -0.038190, 0.026997, 0.043926, 0.090333, 0.062004, -0.067112, -0.040312, -0.064961, -0.048816, -0.001669, 0.056755, 0.067488, 0.024803, -0.076225, -0.011288, -0.008246, 0.037767, -0.018412, 0.008660, -0.074318, 0.014016, 0.038643, 0.044808, -0.021040, -0.024571, 0.040363, 0.013653, -0.096314, 0.050159, -0.027156, 0.021131, -0.015638, -0.040538, 0.049178, 0.068364, 0.027232, 0.007820, 0.079274, -0.000000, -0.015684, 0.115302, 0.006749, 0.013653, 0.035587, 0.075677, 0.045808, 0.068757, 0.036378, 0.008928, -0.048769, 0.021570, -0.045415, -0.014897, -0.064616, -0.023672, -0.015493, -0.031591, -0.051436, 0.015809, -0.028119, 0.045243, 0.037321, -0.038871, -0.017310, 0.038608, 0.001708, 0.014901, 0.027586, 0.001766, -0.036039, 0.027166, 0.086721, -0.012363, -0.082229, 0.040083, 0.075334, -0.006316, -0.054697, 0.097500, 0.065689, 0.026781, 0.012343, 0.046785, 0.016620, -0.060413, 0.086211, -0.002216, -0.033353, -0.015774, -0.032143, -0.035645, 0.051768, -0.016655, -0.059034, 0.076024, 0.000169, 0.027319, -0.001320, 0.020874, 0.067505, -0.010861, 0.102361, 0.015107, 0.138366, -0.010959, 0.028823, -0.043660, -0.077114, -0.057472, -0.039082, 0.019656, -0.018851, -0.039940, 0.009761, -0.025114, -0.148611, 0.004113, 0.044708, -0.060160, -0.027269, -0.068481, 0.027418, -0.001474, 0.059510, 0.080855, 0.081774, -0.057038, 0.003286, 0.023572, -0.036846, -0.005788, 0.039872, -0.061589, 0.101594, 0.000000, -0.018065, 0.015956, -0.053585, -0.038346, -0.055533, -0.105530, -0.037413, 0.078947, -0.030575, 0.045329, 0.077024, -0.031139, -0.030022, 0.026620, -0.045108, -0.010048, 0.022795, -0.026277, -0.038568, -0.017865, 0.033930, 0.022226, 0.007046, 0.003883, 0.067150, 0.038266, 0.015362, 0.056246, 0.032228, 0.016471, 0.018265, 0.003750, 0.027180, 0.072716, 0.043489, 0.114766, 0.041878, 0.061553, 0.000147, -0.027635, 0.016882, 0.087124, 0.028309, -0.029226, 0.027221, -0.068049, 0.001143, -0.069450, -0.027005, 0.044924, 0.040285, 0.001483, 0.023215, 0.001130, -0.009907, 0.081483, -0.029678, 0.040829, -0.085853, 0.027445, 0.037564, -0.007999, -0.025255, -0.031162, 0.111152, 0.020699, 0.027250, -0.060447, 0.050915, -0.019082, 0.083256, 0.099488, 0.025154, -0.103275, 0.056193, 0.014609, -0.061493, 0.087284, -0.040278, 0.049070, 0.001486, -0.049754, -0.026602, -0.021320, 0.010277, 0.056128, 0.094797, 0.029757, 0.030542, -0.087980, -0.103100, 0.015332, -0.090616, 0.024678, 0.009135, -0.000000, -0.063267, 0.011144, -0.155683, -0.042241, 0.101515, -0.046449, -0.106191, -0.066784, -0.056342, -0.100640, -0.118320, 0.011685, -0.026355, 0.045159, -0.035466, -0.103385, -0.027176, -0.014060, -0.046087, 0.039042, 0.033189, 0.001793, 0.060242, 0.077466, 0.088578, 0.042227, 0.000320, -0.030300, -0.005424, -0.004009, -0.041517, -0.080366, -0.019522, 0.045592, 0.016674, -0.014082, 0.023171, 0.028710, -0.042938, 0.007013, 0.004480, -0.041929, 0.014193, -0.006778, 0.060086, 0.011324, 0.047104, 0.002841, -0.034910, -0.075977, -0.027194, 0.094014, -0.073438, -0.113976, -0.041277, -0.120874, -0.000468, 0.017021, -0.107472, 0.036012, -0.047695, 0.017010, -0.017051, -0.064404]),
    ('routine_inspection', 'routine inspection preventive maintenance nominal',
     [-0.041079, 0.069815, 0.073304, -0.037093, 0.023258, -0.034561, 0.065676, -0.018346, -0.101208, 0.023836, 0.035188, -0.013553, -0.005736, -0.019339, -0.117596, -0.038116, 0.045379, -0.035051, 0.009006, 0.002205, -0.003179, 0.037847, 0.017419, 0.000062, -0.118066, 0.060451, -0.004843, 0.002932, 0.077540, -0.022224, -0.079686, 0.107556, -0.014092, -0.006767, 0.041424, 0.010648, -0.004194, -0.010237, -0.004517, -0.029392, -0.009044, -0.056404, -0.044503, -0.042577, 0.031007, 0.051050, -0.000710, -0.066506, 0.029766, 0.003349, 0.007920, 0.040985, 0.059118, 0.011072, 0.057111, -0.012369, 0.071140, -0.115527, -0.035658, -0.019392, 0.072002, 0.000757, -0.065337, -0.003917, 0.022301, 0.015297, -0.048105, -0.061347, 0.075694, 0.077735, -0.063259, -0.010690, -0.007714, 0.023524, 0.020567, 0.033350, -0.025420, -0.041231, 0.057358, -0.124068, -0.077855, 0.003166, 0.055808, 0.051426, 0.019603, 0.016532, 0.029909, -0.013497, 0.078957, 0.014546, 0.079076, 0.016484, 0.018677, -0.056245, -0.019952, -0.030381, -0.026012, -0.041941, -0.013918, 0.051728, 0.065804, 0.009878, 0.023056, 0.004688, -0.041359, -0.010347, 0.039510, -0.010239, 0.023099, -0.029687, 0.008475, 0.043460, -0.014003, -0.089619, -0.035742, 0.014562, 0.017023, 0.022499, 0.058619, -0.039911, 0.106993, 0.012187, 0.028055, -0.060700, 0.124001, -0.005017, 0.106510, -0.000000, 0.060912, 0.021796, -0.044432, -0.066828, -0.054487, 0.016599, 0.033798, 0.003783, 0.096797, 0.029131, 0.064510, 0.080467, -0.075896, -0.088827, 0.025802, 0.037329, 0.058352, 0.073346, 0.012019, 0.038595, -0.005389, -0.087653, 0.012387, 0.013762, 0.095026, -0.000859, -0.017696, 0.050900, 0.001332, 0.062728, 0.129199, 0.092552, 0.002948, 0.028688, -0.088749, -0.033636, -0.076676, 0.035681, -0.009976, -0.047088, -0.028309, -0.074693, 0.038489, -0.024576, 0.058925, -0.031072, 0.031388, 0.013030, -0.072977, -0.000072, -0.004455, -0.001519, -0.054533, 0.004500, -0.069157, 0.037488, 0.051378, -0.016248, -0.070464, 0.021842, -0.026598, 0.039733, -0.003658, 0.044375, -0.002649, -0.059996, 0.034401, -0.042698, 0.037094, 0.002660, -0.088342, -0.018908, 0.005821, 0.047285, -0.005980, -0.048823, 0.019012, 0.060148, 0.015381, -0.008692, -0.045008, 0.003366, 0.043177, -0.038061, 0.031814, 0.004153, 0.032777, 0.039661, -0.030469, 0.004300, 0.009961, 0.026605, -0.058870, 0.064815, 0.000482, -0.000000, 0.072187, -0.019346, 0.033805, 0.038263, -0.091528, -0.043110, -0.044327, 0.006266, -0.016912, -0.013383, -0.026790, -0.022760, -0.114053, -0.054505, -0.054884, 0.005527, -0.008207, -0.057197, -0.026299, 0.038450, 0.014689, 0.061042, -0.078226, 0.031238, -0.119831, 0.064827, -0.096839, -0.009408, -0.030055, 0.008079, -0.071662, -0.022515, 0.029631, 0.071364, -0.008797, -0.065615, -0.016047, 0.029543, -0.066739, 0.064795, 0.089134, 0.033822, 0.009952, 0.054109, -0.002656, -0.100757, 0.004890, -0.059770, -0.074908, -0.032856, 0.015875, -0.030697, 0.030304, 0.015356, -0.006482, 0.109478, -0.038553, -0.024799, -0.158024, 0.142984, 0.071728, 0.027840, 0.004422, 0.006040, 0.019275, 0.016526, 0.074731, 0.023421, 0.045845, -0.056739, -0.013508, 0.067004, -0.065772, -0.094674, -0.023939, -0.004214, 0.033737, -0.084271, 0.001986, 0.055240, -0.051858, -0.001013, -0.021625, 0.114492, -0.024210, -0.070529, -0.018667, -0.011885, 0.049191, 0.052570, 0.004537, 0.070687, -0.110544, -0.054217, -0.065398, -0.000000, -0.033688, 0.016528, -0.001780, -0.045450, 0.062785, -0.093745, 0.000097, 0.035273, -0.057695, -0.017784, -0.034703, -0.009520, -0.023456, 0.009282, -0.005461, -0.086031, -0.011599, 0.087286, -0.160537, 0.031920, -0.003692, -0.008127, -0.008060, 0.020193, -0.085927, -0.016184, 0.089340, -0.014914, 0.044894, 0.105709, 0.011757, 0.022040, 0.072626, 0.006905, 0.061125, -0.013440, 0.054047, -0.078916, 0.049720, -0.008889, -0.047383, -0.000321, -0.003135, 0.052314, 0.031020, 0.002578, -0.097387, -0.056623, -0.045642, -0.073250, 0.050189, -0.019313, 0.014143, 0.111004, -0.056809, -0.003507, 0.083583, -0.014680, -0.002862, 0.026654, -0.019212, -0.026945, 0.055756, 0.003061])
ON CONFLICT (search_string) DO UPDATE SET
    query_name = excluded.query_name,
    embedding  = excluded.embedding;

Summary

In this step, you:
  • Created tables with structured + semi-structured schemas
  • Imported JSON data directly from an external source

This dataset will be used in the next sections to explore querying, analytics, and vector operations in CrateDB.