Most analytics platforms struggle to support the full range of operations required for complex real-world problems. Spatial, graph, time series, and vector search capabilities are often siloed into separate tools, forcing users to stitch together workflows across multiple systems—creating inefficiencies, bottlenecks, and integration challenges. Kinetica eliminates this friction by unifying these advanced analytics within a relational framework. Users can perform deep analytical operations—such as computing isochrones, solving shortest paths, and aggregating spatial zones—within a single SQL query, streamlining decision-making that would otherwise demand multiple systems and custom engineering. This blog demonstrates how Kinetica’s built-in capabilities enable high-speed reachability and coverage analysis with remarkable efficiency.

Figure 1 : The animation is created from the agglomerated isochrones from 500+ fire stations in the Seattle area within 1,2,3,4,5 and 10 minutes successively by applying the SQL statement shown in Figure 3.

How long does it take for a fire truck to reach a certain location from all available stations is an important  question for many interested parties; for the home owner to protect his/her house, for the city officials to ascertain disaster reflex capabilities, for the insurance industry to set premiums on a sensible criterion, etc.  Similar questions can also arise for individual businesses so that new franchise locations could optimally be chosen that would make sense for customer coverage and improve overall competitiveness.

Understanding Isochrones and Reachability Zones

In the case of fire stations, the reachability zone from each station within some fixed time limit is calculable with the road network topology (graph) and traffic conditions (graph weights). These reachability zones are often depicted on the road network as polygons or closed contours, which are simple isolines of travel times and more specifically called as isochrones (see left figure).  The locations reachable within 120 secs from a fire station could also be reached by another fire station in the proximity, possibly in less than two minutes. Hence, the overlap zone of isochrones among the available fire stations can be unioned to discern the coverage area by all fire stations. This would require computing isochrones from all stations – potentially tens of thousands, say, for the continental United States. Each isochrone solve is essentially a Dijkstra algorithm that would stop propagating up-to the chosen time threshold of 1,2,..10 minutes, etc. It is worth noting that the reachability is calculated using the road network, not geodesic distance. Therefore, the success of this particular use case depends on the scalability aspect of the solution.

Kinetica’s Scalable Approach to Isochrone Computation

Kinetica has implemented an all-in-house distributed graph (geo and social) database that works in tandem with the distributed relational (OLAP) database [1-5]. A multi-threaded batch version of the isochrone solver where polygons are generated from the concave hulling of the network locations within the isochrone time limit is specifically implemented with the scalability requirement in mind. Kinetica has graph and OLAP as one product so that users would not need to patch libraries/tools, but in one simple SQL statement, this computationally intensive use case could be run very efficiently. 

Figure 2: Isochrone coverages from 500+ fire stations around the Seattle metro area in 1,2,3,4 minute intervals.

Performance and Efficiency of Kinetica’s Solution

The area isochrone computation for the ‘Fire Stations’ in the SQL statement shown in Figure 3 takes less than two seconds on our production cluster for 500+ fire stations found among 104 million places FSQ dataset within the Seattle metro area WKT polygon. The SQL statement can easily be modified by two simple changes; one for the isochrone value, depicted below as 120 seconds, and the other one is the search string for the place’s name – For instance, searching for Starbucks coverage, it is  just enough to change the search line in the SQL statement to ‘name LIKE ‘%Starbucks%’’ – Figure 4 below shows the result of the Starbucks isochrones in 5km range across the entire US road network. Almost 55k entries are run in the isochrone computation in batch mode and the resulting polygons are agglomerated and unioned – all less than 30 secs on our 8 node cluster. One other important caveat of running the shortest path solver for businesses is that instead of the direction of the solve going off of the business centers, we need to consider all other locations in the graph going towards the businesses. Hence, the solver is modified to be an ‘INVERSE_SHORTEST_PATH’ solver where during the solve, the directionality of the graph edges are temporarily inverted (the idea of this solver is a trademark algorithm under Kinetica-Graph analytics suite).

-- Finding 4 min aggregated coverage for fire stations in Seattle metro
SELECT ST_DISSOLVEOVERLAPPING(poly) as WKT
 FROM
 (SELECT ST_CONCAVEHULL(ST_COLLECT_AGGREGATE(ST_MAKEPOINT(x,y)),0.8) AS poly
    FROM 
    SOLVE_GRAPH
    (GRAPH => 'public.osm_seattle_metro_road_network',
      SOLVER_TYPE => 'SHORTEST_PATH',     
      SOURCE_NODES => INPUT_TABLES(
           (SELECT geom AS WKTPOINT from public.fsq_places
            WHERE
                  STXY_INTERSECTS(longitude,latitude,
                   'POLYGON ((-124.7228 47.03614, -121.16819 47.03614, -121.16819 48.578765, -124.72282 48.57876, -124.72282 47.03614))') 
                AND
                  STRING(fsq_category_labels) LIKE '%Fire Station%'
           ) 
      ),
      OPTIONS => KV_PAIRS(max_solution_radius = '240')
    )
    WHERE z < 240
    GROUP by SOURCE)

Figure 3: Unioned Isochrone polygon computation and aggregations all in one SQL statement from FSQ places public data for all fire stations in Seattle within 120 seconds.

Figure 4: Starbucks isochrones in 5km across the US more than 52.7k – (right) Isochrones zoomed in around the northeast. This is computed in less than 30 secs including finding Starbucks locations, computing, aggregating for forming individual polygons and unioning (dissolve overlap) as shown in one line SQL in Figure 3.

Competitive Insights for Business Decisions

Finally, if we run the SQL statement in Figure 3, searching for 2 minute isochrone coverages for ‘Starbucks’, ‘Peets’ and ‘7-Eleven’ cafes separately, and superimpose the results to have an idea of where the overlaps are occurring as shown in Figure 5 that would give a great insight for business decisions among competitive vendors.

Figure 5: Competitive 2-minute isochrone coverages for various Cafe businesses in Seattle metro. 

Lastly,  The simulation of a central location being served from 500+ fire stations is demonstrated by the use of the inverse shortest path batch solves in the following Kinetica-Graph SQL query below:

-- Find the fastest fire stations to reach to a disaster x,y location among all available over seattle road network
-- Click on  
DROP TABLE IF EXISTS batch_sssp;
EXECUTE FUNCTION
MATCH_GRAPH(
GRAPH => 'public.osm_seattle_metro_road_network',
SAMPLE_POINTS => INPUT_TABLES(
   (SELECT ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY fsq_place_id) AS OD_ID,
           ST_GEOMFROMTEXT('POINT(-121.958755 47.177694)') AS ORIGIN_WKTPOINT,
           geom AS DESTINATION_WKTPOINT
    FROM public.fsq_places
        WHERE  STXY_INTERSECTS(longitude,latitude,'POLYGON ((-124.7228 47.03614, -121.16819 47.03614, -121.16819 48.578765, -124.72282 48.57876, -124.72282 47.03614))') AND
        STRING(fsq_category_labels) LIKE '%Fire Station%')
   ),
SOLVE_METHOD => 'match_batch_solves',
SOLUTION_TABLE => 'batch_sssp',
OPTIONS => KV_PAIRS(inverse_solve = 'true', output_tracks = 'true', svg_speed = '50', svg_basemap = 'true'));

Figure 6: Simulation of finding the fastest fire stations to reach a disaster location among all available.

Animation is an SVG file in the response field of the Restful Graph solver api whose speed can also be adjusted via the option ‘svg_speed’ as seen in Figure 7 below:

Figure 7: SVG animation of the batch solve simulation for fire emergencies.

This Would Be Impossible with Any Other Single Solution

Kinetica unifies spatial, graph, and time-series analytics within a single SQL query, making complex reachability and coverage analysis both possible and highly efficient. Computing isochrones to assess coverage typically requires multiple tools and extensive data engineering, but with Kinetica’s speed and simplicity, it can be executed seamlessly using just a tiny SQL query.

Try It Out

The entire SQL workbook with its data required to replicate the above use case is available on our free SaaS instance as a featured example and in kinetica’s example repo.

References

1. B. Kaan Karamete, Louai Adhami, Eli Glaser, ‘A fixed storage distributed graph database hybrid with at-scale OLAP expression and I/O support of a relational DB: Kinetica-Graph’, https://arxiv.org/abs/2201.02136 [CS.DB], 20 pages – Jan 6, 2022.
2. B. Kaan Karamete, Eli Glaser,  ‘An Ad-hoc graph node vector embedding algorithm for general knowledge graphs using Kinetica-Graph’, https://arxiv.org/abs/2407.15906  [CS.LG, CS.AI] July 23, 2024
3. B. Kaan Karamete, Eli Glaser,  ‘Novel data structures for label based queries specifically efficient for billion+ property graph networks using Kinetica-Graph’,   https://arxiv.org/abs/2311.03631 [CS.DS], 11 pages – Nov 7, 2023 
4. B. Kaan Karamete,  Eli Glaser, ‘Optimal routing algorithm for trips involving thousands of ev-charging stations using Kinetica-Graph’,  https://arxiv.org/abs/2206.06241 [CS.DC, MATH.OC], 13  pages – May 19, 2022.
5. Karamete, BK., Adhami, L., Glaser, E., ‘An adaptive Markov Chain Algorithm applied over map matching of vehicle trip GPS data’, Journal of Geospatial Information Science. 24(3): 484-497 (2021), Taylor & Francis. https://www.tandfonline.com/doi/epdf/10.1080/10095020.2020.1866956?

The post Finding Competitive Coverage of the FSQ Places Dataset Over Road Networks Using Batch Isochrone Computations in One Tiny SQL Statement appeared first on Kinetica - The Real-Time Database.

In an ideal scenario, all operational data would reside in memory, eliminating the need to read from or write to slower hard disks. Unfortunately, system memory (RAM) is substantially more expensive than disk storage, making it impractical to store all data in-memory, especially for large datasets.

So, how do we get the best performance out of this limited resource? The answer is simple: use a database that optimizes its memory use intelligently.

Prioritizing Hot and Warm Data

Kinetica takes a memory-first approach by utilizing a tiered storage strategy that prioritizes high-speed VRAM (the memory co-located with GPUs) and RAM for the data that is most frequently accessed, often referred to as “hot” and “warm” data. This approach significantly reduces the chances of needing to pull data from slower disk storage for operations, enhancing the overall system performance.

The core of Kinetica’s tiered storage strategy lies in its flexible resource management. Users can define tier strategies that determine how data is prioritized across different storage layers. The database assigns eviction priorities for each data object, and when space utilization in a particular tier crosses a designated threshold (the high water mark), Kinetica initiates an eviction process. Less critical data is pushed to lower, disk-based tiers until space utilization falls to an acceptable level (the low water mark).

This movement of data—where less frequently used information is shifted to disk while keeping the most critical data in high-speed memory—ensures that Kinetica can always allocate sufficient RAM and VRAM for current high-priority workloads. By minimizing data retrieval from slower hard disks, Kinetica can sidestep the performance bottlenecks that typically plague database systems.

The Speed Advantage: Memory vs. Disk

The performance gains achieved by this approach are clear when you consider the speed differential between memory and disk storage. To put things into perspective, reading 64 bits from a hard disk can take up to 10 million nanoseconds (0.01 seconds). In contrast, fetching the same data from RAM takes about 100 nanoseconds—making RAM access 100,000 times faster.

The reason for this stark difference lies in the nature of how data is accessed on these storage devices. Hard disks use mechanical parts to read data, relying on a moving head that accesses data in blocks of 4 kilobytes each. This block-based, mechanical retrieval method is inherently slower and incurs a delay every time data needs to be accessed. On the other hand, system memory allows direct access to each byte, without relying on mechanical parts, enabling consistent and rapid read/write operations.

Kinetica’s memory-first strategy leverages this inherent speed advantage by prioritizing RAM and VRAM for all “hot” data. This not only reduces the reliance on slower storage but also ensures that analytical operations can be performed without the bottleneck of data loading from disk.

Managing Costs Without Sacrificing Performance

While memory offers significant speed advantages, the flip side is its cost. Storing all data in RAM is prohibitively expensive for most organizations, especially when dealing with terabytes or petabytes of data. Kinetica balances this cost with two key approaches:

1. Intelligent Tiering: As mentioned earlier, Kinetica’s tiered storage automatically manages what data should stay in high-speed memory and what should be moved to lower-cost disk storage. This ensures that only the most crucial data occupies valuable memory resources at any given time.

2. Columnar Data Storage: Kinetica also employs a columnar data storage approach, which enhances compression and enables more efficient memory usage. By storing data in columns rather than rows, Kinetica can better optimize its memory footprint, allowing more data to be held in RAM without exceeding cost limits.

Conclusion

By adopting a memory-first, tiered storage approach, Kinetica effectively addresses the inherent challenges of traditional database systems. Its ability to prioritize RAM and VRAM for the most frequently accessed data—while intelligently managing less critical data on lower-cost disk storage—allows for faster analytics without the performance penalty of constant disk access.

This approach ensures that Kinetica remains an efficient, high-performance database solution, capable of handling complex analytics at speed, without requiring prohibitively large memory investments. In essence, Kinetica provides the best of both worlds: the blazing speed of memory with the cost efficiency of tiered storage management.

The post Kinetica’s Memory-First Approach to Tiered Storage: Maximizing Speed and Efficiency appeared first on Kinetica - The Real-Time Database.

With Rockset being sunsetted by September 30th, many of its customers are left in the lurch, seeking a reliable alternative for their real-time data analytics needs. Kinetica stands out as the optimal choice, engineered specifically for real-time data analytics.

Why Choose Kinetica for Your Rockset Migration?

Kinetica leverages GPU acceleration to ingest millions of records and execute complex OLAP, spatial, time series, graph analytics and vector search using SQL. Our platform is designed to match or outperform Rockset in handling real-time data analytics and similarity search, providing a seamless transition with enhanced performance.

Start Free With Kinetica Cloud in Just Minutes

• Sign Up for Kinetica: If you haven’t already, setting up a Kinetica account is quick and easy. Once signed in, you can either launch a database instance on our shared free forever cluster or opt for a pay-as-you-go model starting as low as $1.80 per hour.

Create a staging S3 bucket

• Set Up an S3 Bucket: Create an S3 bucket on AWS to temporarily host your Rockset data before importing it into Kinetica. Follow these instructions to set up your S3 bucket.

Export Your Rockset Data to AWS S3

• Export Data from Rockset: Use the INSERT INTO s3 function to export your data from Rockset to your AWS S3 bucket in Parquet or CSV format. Refer to Rockset’s documentation for detailed steps on exporting data.

-- Example export command in Rockset
INSERT INTO s3://your-bucket-name/your-data-file
INTEGRATION = 's3export'
FORMAT = (TYPE='PARQUET', INCLUDE_QUERY_ID=true)
SELECT * FROM your_rockset_collection;

Load Data From AWS S3 Into Kinetica

• Import Data into Kinetica: Follow the instructions here to load your data from the S3 bucket into Kinetica. Ensure your data is in the correct format and structure as per Kinetica’s requirements.

LOAD DATA INTO rockset_migration.table1
FROM FILE PATHS 's3://your-bucket-name/your-data-file'
FORMAT PARQUET
WITH OPTIONS (DATA SOURCE = 's3_ds')

Migrate Your Queries

• Transition Your Queries: Kinetica is ANSI SQL compliant, making it straightforward to migrate your queries from Rockset. Here’s our SQL documentation to help you adapt your queries to Kinetica.

Need Help?

• Contact Support: If you have any questions or need assistance during the migration process, feel free to reach out to us at support@kinetica.com. Our team is ready to help you ensure a smooth transition.

The post Migrating from Rockset to Kinetica: A How-To Guide appeared first on Kinetica - The Real-Time Database.

Real-time Multimodal Capabilities for AI Copilots

Kinetica’s database engine is uniquely suited for AI copilots because it excels in two critical areas: analytical range and low-latency responses. Conversational inquiries can lead to unpredictable and diverse types of queries. Kinetica supports a wide array of analytical tasks, including spatial, OLAP, graph, time series, and vector search, ensuring comprehensive analytical coverage required to support a conversation mode of inquiry.

Additionally, when dealing with enterprise-scale data, maintaining a conversational flow requires fast query responses. Kinetica’s architecture, leveraging modern CPUs and GPUs, guarantees high-speed processing, allowing for quick and seamless transitions from language to insight. 

Furthermore, the speed with which data can be ingested into and extracted from Kinetica is exceptionally fast due to our distributed architecture. This ensures low data latency, complementing our fast query response times and making Kinetica an ideal platform for powering AI copilots.

From English to Insight: A Seamless Journey

An LLM does not have any context about your data. To generate the queries required to draw insights, it needs to be provided this information. Over the last year, Kinetica has introduced new capabilities that allow users to catalog their data, describing tables, columns, and relationships. When a user types in a question a Generative AI application will send Kinetica a request for a data context. Kinetica will dynamically construct this context in a way that respects enterprise security requirements down to the row and column level. This ensures that LLMs can frame queries correctly while guaranteeing that sensitive information is not exposed to the wrong user. 

Kinetica also introduced advanced vector search capabilities that leverage GPU and CPU parallelization. This ensures that vector search queries benefit from the same high performance as other queries on Kinetica. We can ingest streams of vector embedding data and query them before indexing, reducing latency and allowing immediate query availability. This capability is essential for building generative AI applications that provide real-time insight as embeddings are generated.

Our customer are already seeing tremendous benefits

Our customers are already using the features described here to power AI copilots that decision makers use to query data in plain English. Our speed supports Gen. AI applications at enterprise scale, providing significant gains in the speed and ease of deriving insights—all without writing a single line of code.

OpenAI’s acquisition of Rockset highlights a significant trend: the growing importance of natural language interfaces for enterprise analytics. Kinetica is uniquely positioned to lead this transformation. With our powerful and versatile database engine, designed for the demands of modern AI applications, we are ready to empower enterprises to turn natural language queries into immediate, actionable insights. As we continue to innovate, our mission remains clear—to provide enterprises with the most cutting edge tools they need to leverage their data.

The post OpenAI’s Acquisition of Rockset: A Bellwether for the Future of Real-time Enterprise Analytics and Vector Search appeared first on Kinetica - The Real-Time Database.

Imagine waking up one morning to find that your local gas station has run dry, leaving you stranded without fuel. This was the harsh reality for millions of Americans in May 2021, when the Colonial Pipeline ransomware attack disrupted the fuel supply across the Eastern United States. DarkSide, a group of hackers, infiltrated the pipeline’s systems, causing widespread panic and fuel shortages. This incident highlights the growing vulnerability of our energy infrastructure to cyberattacks.

As the energy sector undergoes digital transformation, it faces evolving threats that outdated cybersecurity tools struggle to combat. Kinetica’s GPU-accelerated analytics solution empowers the energy sector with real-time capabilities to detect, analyze, and mitigate the impact of cyber threats. This enhances both incident response and cyber resilience, ensuring continuous protection and operational integrity.

Challenges to Cybersecurity & Kinetica

Cyberattacks on the energy sector have been escalating, with incidents increasing from 499 per week in early 2022 to 1101 by the end of the same year.(“”) Despite this surge, many attacks go undetected or unreported, leading to severe consequences.

As energy companies embrace digital transformation and emerging technologies to expand and manage operations, they also expose themselves to greater cyber risks. The World Economic Forum and International Energy Agency highlight the industry’s transition from analog to digital, centralized to distributed, and fossil-based to low-carbon, emphasizing the critical need for robust cybersecurity measures.

However, the sector faces significant challenges, including:

• Limited cybersecurity expertise
• Inadequate data protection due to large data volumes
• Difficulty integrating security tools and scaling solutions

These challenges create two broad scenarios:

1. Energy firms may ignore cybersecurity threats altogether.
2. Firms aware of cyber threats may lack sufficient security measures.

In the latter case, Kinetica can help by identifying threats faster than other solutions and then mitigating their spread using graph network analysis.  

Colonial Pipeline Ransomware Attack (2021)

Attack Details

The Colonial Pipeline ransomware attack, orchestrated by the DarkSide group, caused significant disruption by halting the operations of a critical fuel supply line in the Eastern United States. The attack led to widespread fuel shortages and panic buying. The hackers gained initial access through a compromised VPN account and employed a double extortion tactic, encrypting the data and threatening to leak stolen information​.

Kinetica’s Potential Role In Mitigation

The attack started because of a compromised password, which Kinetica could not have prevented. However, immediately after the initial access, there is a critical window for threat mitigation. Kinetica’s advanced real-time analytics capabilities could have played a significant role during this period:

• Real-Time Network Traffic Analysis: Kinetica could have continuously monitored network traffic to flag suspicious activities and detect communications between the malware and its command-and-control servers. This would have enabled faster identification of the breach.
• Graph Analytics for Isolation: Using its graph analytics engine, Kinetica could have mapped and analyzed the network to quickly isolate infected nodes. This immediate isolation would have prevented the malware from spreading further, significantly mitigating the attack’s impact.
• Anomaly Detection: By analyzing patterns and anomalies in network behavior, Kinetica could have detected irregular activities indicative of a ransomware attack, enabling prompt response and containment efforts.

By leveraging these capabilities, Kinetica could have provided the necessary insights and actions to reduce the severity of the attack.​.

Why Kinetica

Cookie-cutter solutions take a one-size-fits-all approach that lacks the ability to address specific vulnerabilities or operational nuances of a particular industry or enterprise. These solutions may not scale well with the changing needs of an organization and an ever-evolving cybersecurity landscape.

Kinetica provides a high-performance engine that is easy to customize to your needs. It can run complex analytics on real-time data to provide the insights needed to protect against cyber threats. This tailored approach ensures that the unique challenges and needs of the energy sector are met, providing a robust defense against potential attacks.

Conclusion

In today’s digital age, the energy sector cannot afford to rely on outdated cybersecurity measures. The Colonial Pipeline incident serves as a stark reminder of the potential consequences of inadequate cyber defenses. By adopting advanced analytics solutions like Kinetica, the energy sector can enhance its cyber resilience, protect critical infrastructure, and ensure continuous operation. Kinetica’s real-time capabilities and customizable solutions make it a vital tool in the fight against cyber threats, saving the energy sector millions of dollars and safeguarding the nation’s fuel supply​.

The post Preventing Cyber Attacks: How Kinetica Can Save the Energy Sector Millions of Dollars appeared first on Kinetica - The Real-Time Database.

NIM packages AI services into containers, facilitating their deployment across various infrastructures while giving enterprises full control over their data and security. By combining these services with Kinetica’s robust compute and vector search capabilities, developers can easily build data copilots that meet rigorous performance and security requirements. The queries below show how you can connect Kinetica to NIM with just a few SQL statements.

-- Create the credentials for connecting to NIM
CREATE CREDENTIALS schema.my_creds 
TYPE = ‘NVIDIA_NIM’
API_KEY = ‘<key>’
ORG_ID  = ‘<org>’

-- Register an embedding model
CREATE REMOTE MODEL schema.nim_nvidia_qa4
OPTIONS
(
NAME = ’nvidia/embed-qa-4’
CREDENTIALS = schema.my_creds 
);

-- Stream embeddings generated by the embedding model into a view in Kinetica
CREATE MATERIALIZED VIEW real_time_embeddings AS 
( 
SELECT * 
FROM GENERATE_EMBEDDINGS 
(
TABLE =>  ‘schema.my_input_table’
MODEL_NAME => ‘schema.my_embed_model’
INPUT_COLUMN_NAMES =>  ‘text_chunk’,
OUTPUT_COLUMN_NAME => ‘vector_embed_from_NIM’  
)
)
REFRESH ON CHANGE

A cybersecurity chatbot

We are developing a cybersecurity chatbot using this stack. The chatbot processes two types of sources:

• Data Sources: High-velocity network traffic data and various forms of cyber threat detection data.
• Documents: Information on different types of attacks, network protocols, and indicators of atypical usage.

Given a user prompt, the chatbot interfaces with tabular data sources and text-based knowledge from documents to provide an appropriate response. The diagram below outlines the architecture of the solution. The diagram below outlines the architecture and information workflow of the solution.

In this scenario, the user interacts with a supervisor agent which is powered by a LLM NIM to search for suspicious activity related to a recent cyberattack. Kinetica is automatically also interfacing with NIM based embedding generation models as documents are inserted into the database to make vector embedding of threat intelligence reports.

Step 1:  The user asks the AI Agent “Any activity on my network that looks related to recent Volt Typhoon attacks?”  Based on the prompt, the supervisor agent selects the appropriate tool from its toolkit of fine-tuned LLMs. 

Step 2: For this prompt, Kinetica first performs Vector search RAG to search recent intel threat reports for relevant IP addresses.  Kinetica returns the following result:

‘..malware communicates with C2 servers using HTTPS and DNS tunneling. Identified C2 servers include: IP addresses: 45.156.169[.]123, 139.162.123[.]138, 139.162.123[.]140..’

This context, including the IP addresses, is then used by Kinetica in a subsequent SQL-RAG query to search the netflow logs for any related activity.  Three recent incidents are found:

{“activity_count” : 3, “start_time”: “2024-05-31T17:38:27Z” }

Step 3: The results from Kinetica are provided to the LLM NIM, along with the original prompt (“Any activity on my network…”) for summarization. Context for main reasoning LLM NIM now contains: 

{“activity_count” : 3, “start_time”: “2024-05-31T17:38:27Z” }
‘..malware communicates with C2 servers using HTTPS and DNS tunneling. Identified C2 servers include: IP addresses: 45.156.169[.]123, 139.162.123[.]138, 139.162.123[.]140..’
“Any activity on my network that looks related to recent Volt Typhoon attacks? ” 

Step 4: The summary from the LLM NIM is sent back to the user:

A: “ “..Analyzing your network activity shows that there appears to be consistent traffic flow with the identified command and control servers starting May 1, 2024...”

Provided below is a simple python example showing how easy it is to leverage Kinetica and the LLM NIM and Embedding to perform advanced RAG operations for more accurate and insightful reasoning. To access NVIDIA NIMs, we leverage the langchain-nvidia-ai-endpoints package that contains LangChain integrations for chat completion and embedding model endpoints that are hosted as NIMs on NVIDIA API Catalog:

#import necessary libraries
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
from langchain.chains.combine_documents import create_stuff_documents_chain
from typing import Dict
from langchain_core.runnables import Runnable, RunnablePassthrough , RunnableSerializable
from langchain_core.messages import HumanMessage
from kinetica.embeddings import KineticaEmbeddings
from langchain_community.vectorstores import Kinetica, KineticaSettings
from langchain_nvidia_ai_endpoints import ChatNVIDIA


# Function for creating a configuration object
def create_config() -> KineticaSettings:
    return KineticaSettings(host=HOST, username=USERNAME, password=PASSWORD)

# Establishing a connection to Kinetica and the Embedding model NIM
connection: KineticaSettings = create_config()

embeddings: KineticaEmbeddings = KineticaEmbeddings()

# Creating a Kinetica object
db: Kinetica = Kinetica(config=create_config(), embedding_function=embeddings, collection_name="cyber_netflow_and_intel_reports")

# Creating a Kinetica retriever for SQL and Vector similarity search
retriever = db.as_retriever()

# System template for how RAG responses populate main reasoning LLM context.
# Note the two entries, one for Vector and one for SQL

SYSTEM_TEMPLATE: str = '''Answer the user's questions based on the below context. If the context doesn't contain any relevant information to the question, don't make something up and just say "I don't know":

<context>
{context}
</context>'''

# Creating the question answering prompt template
question_answering_prompt: ChatPromptTemplate = ChatPromptTemplate.from_messages([
        (
            "system",
            SYSTEM_TEMPLATE,
        ),
        MessagesPlaceholder(variable_name="messages"),
    ])

# Create the LLM NIM and the document chain 
llm: ChatNVIDIA = ChatNVIDIA(nvapi_key=nvapi_key, model="meta/llama3-70b-instruct")
document_chain: Runnable[dict[str, Any], Any] = create_stuff_documents_chain(llm, question_answering_prompt)


# Function to parse retriever output and prepare context text for main reasoning LLM
def parse_retriever_output(params: Dict[str, Any]) -> Dict[str, Any]:
    return params["messages"][-1].content

# Creating a retrieval chain
retrieval_chain: RunnableSerializable = RunnablePassthrough.assign(
    context=parse_retriever_output | retriever,
).assign(
    answer=document_chain,
)

# Question to send to the retrieval chain
question: str = "What can you tell me about Volt Typhoon in terms of the suspected actors and indicators of compromise based on my network activity?"

# Generate an answer
response = retrieval_chain.invoke({
    "messages": [HumanMessage(content=question)],
})

print(response["answer"])

Output based on Real-time network activity and intelligence reports using Kinetica real-time RAG and NVIDIA Inference Microservices

**SUSPICIOUS ACTIVITY DETECTED:** 
Analyzing your network activity shows that there appears to be consistent traffic flow with the identified command and control servers starting May 1, 2024.


Here's what's known about the suspected actors and indicators of compromise that match your network activity:


**Suspected Actors:**
* The Volt Typhoon campaign is attributed to a Chinese advanced persistent threat (APT) group, also known as APT10 or Stone Panda.
**Matching Indicators of Compromise (IOCs):**
**Command and Control (C2) Servers:** The malware communicates with C2 servers using HTTPS and DNS tunneling. Identified C2 servers include:
   + IP addresses: 45.156.169[.]123, 139.162.123[.]138, 139.162.123[.]140
   + Domains: techsupport[.]online, softwareupdates[.].space, windowsdefender[.]pro

Kinetica’s RAG engine can complete the analytic and vector search tasks against datasets that represent up to the second network activity and intelligence reports and are billions of rows in size. Kinetica leverages its advanced GPU-accelerated engine to complete these queries in under a second. The integration with NVIDIA Inference Microservices for embedding generation and LLM inference is a critical part of this stack since it makes it easy to couple our low latency compute with containerized inferencing and embedding generation services that are secure, performant and enterprise ready. This enables us to build more sophisticated and responsive AI applications that can leverage large enterprise relational data and document datasets for next level accuracy and insight.

Talk is the future

Harnessing generative AI to extract insights from enterprise data represents a new and exciting opportunity. However, building enterprise-ready AI applications requires a comprehensive ecosystem of robust data capabilities where performance is more important than ever. By combining Kinetica’s compute power with NVIDIA GPUs and the advanced AI services provided by NVIDIA Inference Micro Services, we are paving the way for making natural language the standard for interfacing with enterprise data.

Try Kinetica now, or contact us to learn more.

The post Kinetica, the GPU-powered RAG Engine for Data Copilots, Integrates with NVIDIA NIM appeared first on Kinetica - The Real-Time Database.

RAG powers applications like ChatGPT, enabling them to act on information beyond their training, such as summarizing news and answering questions about private documents. By storing and retrieving relevant information to augment an LLM’s knowledge, vector databases played a crucial role in the first phase of Generative AI, facilitating the adoption of tools like ChatGPT. 

So why are vector databases being replaced???

While some vector databases will continue to provide value, they are increasingly being replaced by a new breed of multimodal retrieval engines that offer more than just vector search. This shift is driven by the limitations of vector databases in two critical areas:

1. Inability to Retrieve Information from Real-Time Sources at Scale

Consider an AI chat application designed to help customers resolve issues in real-time. For this application to be valuable, it must reference information from real-time sources, such as notes on the customer’s problem, similar incidents reported by other customers, and current backend system logs that might explain why these issues are occurring. This requires the retrieval engine to process millions of real-time records in seconds.

However, such AI chatbots don’t exist today because most databases, including those specializing in vector data, must undergo a time-consuming indexing process when inserting new data. This indexing process clusters similar data together, making retrieval tasks easier. Depending on the database and data scale, indexing can take several minutes to hours, preventing AI applications from accessing the necessary insights to resolve issues in real time. As a result, users often end up resorting to the all-too-familiar refrain when dealing with bots: “Talk to a representative.”

2. Inability to retrieve information from tabular data

Most insights needed to answer business questions are represented in tabular data rather than text. However, vector search only works with text-based sources. As conversational AI expands into enterprise use cases, it will increasingly need to draw referential information from tabular data.

This requires a retrieval engine capable of executing ad-hoc analytics on large enterprise data to extract relevant insights. Very few databases can perform this function effectively.

You Need a Real-time Retrieval Engine

Act on real-time data instantly

To keep pace with the demands of generative AI, a retrieval engine must ingest and act on real-time data with minimal delay. Traditional databases reliant on extensive indexing can’t react quickly enough. Instead, a future-proof retrieval engine should be capable of loading millions of real-time records per second, making this data immediately available for both analytics and search. This capability must extend to both tabular data and vector embeddings.

Execute Complex Multimodal Retrieval in Seconds

A defining characteristic of generative AI powered apps is the ad-hoc nature of the conversation. It is impossible to predict the types of questions that a user might pose. As a result sophisticated applications require a retrieval engine that can do many things fast. This includes vector search, but more importantly, it includes the ability to draw analytic insights from real-time and historical data.

To answer user questions a retrieval engine should seamlessly execute complex multimodal analytics in real-time, drawing insights from diverse data types—spatial, time series, graph data, and vector embeddings—without any pre-preparation. And since it is hard to predict the direction that a conversational inquiry of data can take, the database needs to provide wide analytical coverage across various domains time series, spatial, graph etc. along with standard OLAP (aggregations, joins, filters etc.) and vector search. 

Do It All Securely At Enterprise Scale

Large enterprises have petabytes of data scattered across thousands of tables and schemas. Conversational modes of inquiry will require an engine that can crunch through vast amounts of data easily. Additionally, for an LLM to generate accurate analytic requests it needs to understand the data that it is trying to draw insights from. So the retrieval engine also has the added responsibility of providing additional semantic context for the data in a way that safeguards enterprise security requirements.

Real-time Multi-modal Retrieval Engines are Hard to Find

Many traditional databases have added support for vector data types. In some cases, this is an improvement over pure vector databases, as they can execute hybrid search queries that enhance the relevance of the returned information by combining vector search with additional analytic filters on metadata.

However, these databases still suffer from the same issues as vector databases. They cannot handle real-time data due to indexing requirements. While they can execute analytics on data, they are too slow to meet the demands of generative AI applications.

As a result, working with databases built for static data and analytics is akin to waiting in a long line before boarding a hypersonic jet. The true potential of speed can only be realized when the retrieval engine can perform vector searches and analytics on real-time data almost instantaneously.

As a final note, the term ‘multimodal’ is often loosely used. Many databases claim to be multimodal simply because they offer standard OLAP functions alongside vector search. However, true multimodal capabilities extend far beyond this. A robust real-time retrieval engine must provide comprehensive analytical coverage across various domains, including:

• Time Series Analysis: Essential for tracking changes and trends over time.
• Spatial Analysis: Crucial for applications involving geographical data and location-based insights.
• Graph Analytics: Necessary for understanding relationships and connections within data.

Try Kinetica

Kinetica is a GPU-accelerated database that is built for real-time workloads. We offer a wide array of analytic functions that are all built from the ground up to harness the massive parallelization offered by GPUs and modern CPUs.

We are the real-time, multimodal retrieval engine that powers conversational AI for enterprise data. Try us out—spinning up an instance of Kinetica on the cloud takes just a few minutes.

The post Gen. AI Needs a Real-time Multimodal Retrieval Engine appeared first on Kinetica - The Real-Time Database.

Such a task demands an analytical engine that can ingest high velocity data streams, execute sophisticated queries to pinpoint critical events and insights, and deliver instantaneous results to be acted upon.

This is precisely the challenge you can address with Kinetica and Confluent. Kinetica is a GPU accelerated database that excels in complex real-time analysis at scale, while Confluent, built upon Apache Kafka, provides robust data streaming capabilities. Together, they forge a powerful architecture that unlocks the full potential of streaming data.

My aim with this blog is to demonstrate the power of Kinetica and Confluent in action in three simple steps. 

1. Stream real time GPS coordinates of 10 vehicles from Confluent into Kinetica. 
2. Set up a materialized view inside Kinetica that uses spatial functions to detect when these vehicles get within 200 meters of certain areas in Washington DC. This view will continuously update to detect events as new GPS data streams into Kinetica.
3. Stream geofencing events as they unfold from Kinetica to a Confluent topic for further action.

You can try all of this on your own by uploading this workbook into your free Kinetica instance. All of the data is open for access. You will need a Confluent Cloud account to complete the last step of this demo, you can set one up for free here.

Step 1: Load data from a Kafka topic on Confluent

The simulated GPS coordinates are being streamed into a Kafka topic on Confluent Cloud. Our first task is to register this data source inside Kinetica. For this we need the location of the confluent cluster, the name of the topic that we would like to ingest from and the credentials required for connecting to that cluster. I have used an optional CREATE CREDENTIAL query to store the connection credentials.

-- Credentials for Kafka cluster
CREATE OR REPLACE CREDENTIAL confluent_creds
TYPE = 'kafka'
WITH OPTIONS (
    'security.protocol' = 'SASL_SSL',
    'sasl.mechanism' = 'PLAIN',
    'sasl.username'='QZN62QB2RBTLW74L',
'sasl.password'='iiJDdKeBzf5ms5EInLvpRslW1zwsTYx9tjZ1pQyVoS+gPGkyNms6eMXPaR6y+GST'
);

-- Create the data source for vehicle locations
CREATE OR REPLACE DATA SOURCE vehicle_locations_source
LOCATION = 'kafka://pkc-ep9mm.us-east-2.aws.confluent.cloud:9092'
WITH OPTIONS (
    'kafka_topic_name' =  'vehicle_locations',
    credential = 'confluent_creds'
);

Now that the data source for the vehicle_locations topic is created we can start loading messages from it into a table in Kinetica.

-- Load data
LOAD DATA INTO vehicle_locations
FROM FILE PATH ''
FORMAT JSON
WITH OPTIONS (
    DATA SOURCE = 'vehicle_locations_source',
    SUBSCRIBE = TRUE,
    TYPE_INFERENCE_MODE = 'speed',
    ERROR_HANDLING = 'permissive'
);

Step 2: Detect when a vehicle comes close to a spatial boundary in DC

I have created a set of arbitrary ‘fences’ in the Washington DC area. These are stored in the dc_fences table. Our next task is to detect when a vehicle comes within 200 meters of any of the fences below.

Additionally, we would like to automate event detection so that new events are detected as they occur. We can do this easily with a materialized view that refreshes any time there is a new GPS record that streams into the vehicle_locations table in Kinetica from the vehicle_locations topic on Confluent Cloud. Inside the materialized view we use a spatial function STXY_DWITHIN to check when a vehicle’s location comes within 200 meters of any of the fences in the dc_fences table.

This is where Kinetica shines, ingesting high velocity streams of data from Confluent Cloud and simultaneously running complex analytical queries on that data to generate real time insights. All thanks to our multi-head, lockless architecture.

CREATE OR REPLACE MATERIALIZED VIEW fence_events 
REFRESH ON CHANGE AS 
SELECT 
    TRACKID, 
    wkt, 
    x, 
    y, 
    TIMESTAMP, 
    CONCAT(CHAR16(TRACKID), CONCAT(' is at ', fence_label)) as event_text 
FROM vehicle_locations, dc_fences
WHERE 
    STXY_DWITHIN(x, y, wkt, 200, 1) = 1 AND 
    TIMESTAMP > NOW() - INTERVAL '10' MINUTE;

With just this one simple query we now have an always on eventing system that automatically detects spatial proximity events as they occur.

Step 3: Stream events from Kinetica back to a new topic on Confluent

You will need an Confluent Cloud account to complete this step. If you haven’t done so already, go ahead and set up a free account. It takes less than a few minutes to create a new topic. You can use the default settings. You also want to set up an API key that you can use to connect to your confluent cluster. Head back to Kinetica once you have an API key and a topic set up.

Back in Kinetica let’s create a data sink that points to the topic we just created. A sink is analogous to a data source the only difference being that we are streaming data out to it from Kinetica. You should also create a separate credential object that uses the username and password from your API key (refer to the first query on how to create one).

CREATE OR REPLACE DATA SINKconfluent_sink
LOCATION = 'kafka://pkc-ep9mm.us-east-2.aws.confluent.cloud:9092'
WITH OPTIONS (
    'kafka_topic_name' =  '<name of your topic>',
    credential = '<Insert your confluent credential object>'
);

The final piece is a Data Stream that will stream any changes in the materialized view we created earlier to the topic that we created on Confluent. This stream will listen for any new records (events) inserted into the materialized view we created earlier and send those to the topic .

-- CREATE A STREAM 
CREATE STREAM fence_events ON fence_events  
REFRESH ON CHANGE
WITH OPTIONS 
(
    DATASINK_NAME = 'confluent_sink'
);

Now we can see these new events streaming from Kinetica into Confluent.

Conclusion

As we’ve seen, the combination of Kinetica’s real-time analytical power and Confluent’s robust streaming capabilities opens up new horizons in data analytics. This short demo only scratched the surface of what’s possible. 

This combination of Confluent’s streaming capabilities and Kinetica’s complex real time analytical capabilities can be applied to a myriad of industries and scenarios, from urban planning and logistics to financial markets and emergency response systems. I encourage you to explore the potential of Kinetica and Confluent in your own projects. 

If you have questions, thoughts, or experiences you’d like to share, please don’t hesitate to reach out or comment below. Let’s unlock the full potential of your data together .

The post Kinetica Confluent a match made in streaming heaven appeared first on Kinetica - The Real-Time Database.

Why This Partnership Matters

Confluent is at the forefront of streaming data technology, offering best-in-class capabilities that make it an industry leader. Kinetica enhances this proposition by ingesting these high velocity data streams and fusing them with contextual data, enabling the execution of complex SQL queries – all in real time. This unlocks opportunities for advanced analytics on real-time data feeds, setting a new standard for immediate, data-driven insights.

Fast Ingest

Kinetica’s multi-head ingest is designed to handle the volume and velocity of Kafka topics effortlessly. Its lockless architecture allows query execution while data is being streamed in. Both of these features together slash data latency significantly.

Contextual Insights

Together, Kinetica and Confluent create an ecosystem where data is not just collected but is swiftly understood within its spatial and temporal context. This partnership reveals insights that were previously hidden, offering a new lens through which data can be viewed and acted upon.

Take Action

Kinetica’s multi-head egress capabilities allow users to stream data back out from Kinetica to a Confluent stream and other downstream applications, allowing organizations to make informed decisions quickly, based on the most relevant and contextual information available.

See It in Action

Explore our latest demonstration: monitoring a fleet of vehicles to identify spatial events in real time. This real-world example showcases how Kinetica’s data analytics seamlessly integrate with Confluent’s data streaming.

This partnership marks a significant milestone in our mission to empower businesses with the real-time insights needed to make decisive, data-informed decisions.

Use the links below to try it out for yourself and see the difference real-time data analysis can make.

The post Kinetica joins the Connect with Confluent Partner Program appeared first on Kinetica - The Real-Time Database.

You’re about to see how Kinetica SQL-GPT enables you to have a conversation with your own data.  Not ours, but yours.  With the built-in SQL-GPT demos, the data is already imported, and the contexts that help make that data more associative with natural language, already entered.  When your goal is to make your own data as responsive as the data in our SQL-GPT demos, there are steps you need to take first. 

This page shows you how to do the following:

1. Import your data
   Load their contents into tables inside Kinetica
2. Create contexts
   These will provide the large language model (LLM) with the semantic information it needs to be able to reference the proper columns and fields in your tables
3. Ask Questions
   Questions are phrased the way you’d say these questions out loud, then run the generated queries on Kinetica to get instantaneous results

STEP 1: Import your Data into Kinetica

Kinetica recognizes data files stored in the following formats: delimited text files (CSV, TSV), Apache Parquet, shapefiles, JSON, and GeoJSON [Details].  For Kinetica to ingest this data from another database or from a spreadsheet, it should first be exported from that source into one of these formats.

With one or more importable data files at the ready, here’s what you do:

1. Log into Kinetica Workbench. If you don’t already have Kinetica setup, you can Create a Kinetica Cloud Account Free:
   
2. From the menu over the left-side pane, click the Files tab.  Hover over the + button, and from the popup, select Add New Folder.
   
3. In the popup window, under Name, enter a unique name, then click Create.  The name you entered will soon appear in the Folders list in the left-side pane, sorted in alphabetical order and preceded by a tilde (~).
   
4. In the Folders list, locate and right-click the name of your new folder.  From the popup menu, select Upload New File.  The Upload File panel will appear.
   
5. From your computer’s file system, choose the file or files you wish to upload into your new Kinetica folder, so that their data may be imported.  The files you chose will be indicated in the grey box in the center of the Upload File panel.
6. Click Upload.  Your data files will be uploaded into Kinetica, and this panel will keep you apprised of their progress.
   
7. In a moment, all the grey progress report bars will turn green, indicating your files are successfully uploaded.  To proceed, click Import.  The Kinetica Filesystem Import panel will appear.
   
8. In the panel marked 1 Setup, the file or files you’ve chosen for import will already have been entered.  Click Next > to continue.
   
9. For the panel marked 2 Source, Kinetica will have inferred which format your data is in already.  If the event it makes an inaccurate assumption, in the Format field, choose the format for your imported data from the list.  When all the options for this panel look correct, click Next > to continue.
   
10. In the panel marked 3 Destination, if you’re operating on the free tier, your edition of Kinetica will only have one schema, whose name will appear in the Schema field.  By default, the name to be applied to the table is the current name of the data file (if you changed that name in step 4, your chosen name will appear here).  When the options here are set, click Next > to continue.
    
11. For the panel marked 4 Configure, Workbench is usually capable of inferring the proper field names and content types (for example, short integer values versus floating-point values) from properly formatted, existing tables.  So you may skip the manual inference step, and click Next > to continue.
    
12. The 5 Summary panel explains in detail what Kinetica is about to do with the data you’re asking Workbench to import.  First, it reviews the options you’ve chosen.  Scroll down to find the CREATE TABLE statement that Kinetica will use to generate the table for the schema, followed by the LOAD DATA INTO statement that will populate the new table with data from the file.  There’s nothing more to import here, so when you’re ready to start importing, click Import.
    
13. The status of the import job will be tracked in the History tab.  Once complete, you’ll see a notification to that effect in the upper right of the panel.

Your imported files should now be accounted for in the Files tab of the Workbench’s left-side pane.  Look under your login name to locate them.  The tables generated from that imported data should now appear in the Data tab of this same pane, in the subcategory Tables & Views, under the name of your schema.

STEP 2: Create Contexts for Imported Tables

With OpenAI’s ChatGPT, you’ve probably experienced the feeling of getting a sub-par response from what you thought was an articulate question.  Language models can’t always infer the information you may have thought was obviously implied, if not explicitly asserted.

For SQL-GPT, you increase the probability of correlation between the generated SQL statements and the data you’ve just imported, by means of contexts.  It’s a tool designed to give the language model the best possible information on the first go.

A context consists of natural-language explanations of the terms relevant to your data, for the benefit of the language model.  In Kinetica, a context is a native database object that is created using the SQL statement CREATE CONTEXT.   Workbench enables you to develop a comprehensive context with the annotations and associations it needs to connect ideas from a natural-language question, with terms in your database.

There are four components of a complete context:

1. Table definition The set of table definitions in a context indicate what tables should be considered when generating SQL responses. The DDL for these tables including the column definitions are sent to the LLM during inferencing. Tables with many columns can quickly consume LLM context space and this should be limited to only what is necessary. You can optionally provide the LLM with a description for the table which is uUsually a single sentence or a brief note explaining what the data represents in its entirety.  For example, “The median annual death rates per capita for United States citizens.”
2. Rules – One or more instructions for the LLM regarding how to process or interpret data.  For example, for geospatial data used in a Kinetica demo that includes the maps of the states, a rule may contain this instruction to apply a specific parameter: “Join this table using KI.FN.STXY_WITHIN()=1”  The language model is capable of interpreting the sentence appropriately.  Another example would advise the LLM to associate a field name airport_code with a phrase used to describe airports, such as “airport code” (without the underscore character) — as in, “Use airport_code to find the routing code for the airport.”
3. Column descriptions – Instructions helping the language model to interpret the meanings of field names, giving it clues for associating their symbols (which may have no language definition at all) with everyday terms and phrases that people generally use, coupled with terms that are specific to the data set  For example, in a data set that includes the perimeter points, or “fences,” for given areas on a map, a good context may include a column named WKT.  It’s not obvious to a language model what that means, or that it’s a common abbreviation for the geospatial abbreviation for Well-Known Text format.  So a good column description might include, “A well-known text description of the spatial outline or perimeter of each fence.”
4. Samples – An appropriate sample would include an example of a natural-language question you would expect yourself or another user to ask, coupled with an actual Kinetica SQL statement you’ve tried successfully in Kinetica.  Samples should reference only the names of tables that exist in the context.  Otherwise, the LLM could generate queries that reference non-existent tables and columns.  You can add as many samples as you think may be appropriate. 
   
   For one sample Kinetica compiled for a demo involving the locations of airports, engineers offered the question, “What is the centroid of DCA airport?”  Here, “DCA” is the IATA location identifier for Reagan Airport in Washington, D.C.  It isn’t obvious that a database should retrieve DCA as an airport code first, so the sample provided offered a clue as to how to do this, using this correct response:
   
   SELECT airport_centroid
FROM llm_demo.airport_positions_v
WHERE airport_code = "DCA";
   
   This is a sample of a generated SQL statement that refers explicitly to Reagan Airport.  It explicitly references the table name airport_positions_v and the field name airport_code, which would already be defined as database objects that exist within the environment.  The LLM is capable of interpreting this instruction as a kind of template, creating a generalization that it can apply for situations where the questions being asked are not specifically about Reagan Airport, but some other airport.

Here’s how to create a full context that refers to imported data:

1. In Kinetica Workbench, from the left pane, click the Data tab.  Then under the Tables & Views category, right-click the name of the table or tables to which the context will refer.  From the popup menu, select Configure. 
   
2. The Edit Table panel will appear.
   
3. In the Name field, highlight the complete table name, then in your browser, right-click and from the popup, select Copy.
   
   
4. In the Data tab, at the bottom of the list of database objects, click the + button beside Contexts.  The Create Context panel will appear.
   
5. In the Context Name field, enter a name for the context object in the database, using only letters, digits, and the underscore (_) character.  This will be the name Kinetica will use to refer to the context.
6. A context may refer to more than one table in a schema and you should add each table that the LLM should consider in responses.  To begin associating a context with a table, click + Add Table.
   
   
7. In the Name field, enter or paste the name of the table you copied earlier.
8. Under Description, type a sentence or phrases that describes the data in this table, making sure you use language and terminology that would likely be included in natural-language questions that refer to this data.
   
9. Rules are optional within contexts, but very helpful.  To add a rule that applies to a table, under Rules, click + Add Rule (you may need to scroll down) then enter the rule into the field that appears above the button.
10. Column descriptions are also optional, although they’re frequently necessary to give the language model enough synonyms and associated terms to deduce references to these columns from regular phrases.  To add a column description for a column, click + Add Column Description.  In the Name field, enter the exact field name for the column you wish to describe.  Then under Description, enter a phrase that includes terms and words that are synonymous with that field name, and that would describe it adequately for a human user.  Repeat this process for as many columns as may require descriptions.
11. To add a rule that applies to the context or the schema as a whole, from the top of the Create Context panel, click + Add Rules.  This opens up a Rules box toward the bottom of the set (you may need to scroll down to see it).  To add the first rule, click + Add Rule, then enter the general rule into the field that appears above the button.  Repeat for as many rules as your schema may require.
12. To add one or more sample pairs, from the top of the Create Context panel, click + Add Samples.  A box marked Samples will appear below the Rules box you opened earlier.  To add the first sample in the set, click + Add Sample.  Then under Question, enter a natural-language question you believe a regular user would pose.  Under Answer, enter the SQL code that you know would yield the results that would satisfy this question.  (You may wish to open a separate Kinetica workbook, to try SQL statements yourself until you get the results you’re looking for, then use the best statement as your answer for the sample.)  Repeat for as many samples as you believe would best benefit your context.
13. When you have populated the context components with enough clues, instructions, and samples, click Create.  Kinetica will process your instructions as context creation statements, and will soon inform you of its results.

STEP 3: Ask SQL-GPT Questions

Now let’s test Kinetica’s SQL generation processes at work.  Here, you’ll be creating a workbook in Kinetica Workbench that accepts your natural-language questions, and produces SQL statements you should be able to use in the Kinetica database.  Here’s how to begin a new workbook:

1. From the left pane of Kinetica Workbench, click Workbooks.
   
2. From the Workbooks tab, point to the + button and from the dropdown, select Add New Workbook.  The Create Workbook panel will appear.
   
3. Under Name, enter a unique name for the workbook.  This is not a database object, so it can have special characters, distinguished upper and lower case, and spaces.
4. Under Description, enter something that describes the purposes of the workbook for your purposes.  This is not a description for a language model or for generative AI training; it’s just for you.
5. Click Create to generate the workbook.  It will soon appear in the Workbench window, and the first worksheet, named Sheet 1, will already have been generated.
   
6. In the field at the top of the panel marked with a light bulb, enter a natural-language question to which you’d like the database to be able to respond.  You don’t need to include any extra syntax, such as quotation marks or SQL reserved words.  Just type a question as though you were entering it into an AI chat window, then click Generate SQL.  The Workbench will respond with a warning and the Context Configuration panel.
   
7. The warning you will see indicates that Kinetica has not yet attached a context to the question you’ve posed.  From the panel that appears, in the left-side list, check the box beside the name of the context you created earlier.  Then click the > button so that it appears in the right-side list.  Then click Update.
8. After the panel disappears, click Generate SQL again.  In a few moments, Kinetica will respond with a SQL statement.  Its first line will be a comment containing the text of the question you posed.
   
9. To test this query on the data you’ve generated is to click the round > (Play) button to the upper left of the new query.  In a very brief moment, Kinetica should respond with a view of data queried from the table or tables you imported.

It’s fair to say at this point, especially at the first go, that Kinetica may respond with something else: perhaps an error, or maybe data that does not actually resolve the criteria.  Artificially intelligent inferencing is not a perfect process.  However, you can improve the efficiency of the results by adding information to your context and trying again.  It may take a few runs to get the correct results.  Keep in mind, as you’re doing this, that the minutes you’re spending improving the language model’s ability to ascertain what your language is trying to say, equate to days and even weeks of work in a conventional ETL project, where engineers work to make databases more responsive to dashboards and ad-hoc queries in real time.

The post How to deploy natural language to SQL on your own data – in just one hour with Kinetica SQL-GPT appeared first on Kinetica - The Real-Time Database.