Categories:
Create new Copilot preset using ConfigMap
Guidelines valid for releases previous to Metallica
This document includes a specific scenario in the process of modifying ATRIA configuration, described in the document Modify ATRIA components configuration
Guidelines to create new Aura Copilot preset in a specific environment through the use of ConfigMap and aura-configuration-api.
It is important to follow the following steps in the correct order:
- Prerequisites
- Create ConfigMap copy
- Create a new preset in atria-model-gateway
- Adjust model params
- Allow Preset Access
- Add a new project in atria-rag-server
- Adjust max_tokens param
- Adjust timeouts in aura-gateway-api and Nginx
- Upload documents and execute the generate-db job
- Restart the deployments
- Update Aura applications configuration via API
- Load original config and deployments rollback
Prerequisites
- Recommended:
kubectlinstalled in your local host.curlinstalled in your local host.jqinstalled in your local host.
Enable ConfigMap
As a prerequisite, we must count on a KUBECONFIG with sufficient permissions and access to the environment.
We have one ConfigMap for each component:
- atria-model-gateway: atria-model-gw-config
- atria-rag-server: atria-rag-config
- aura-gateway-api: aura-gateway-api
- aura-services: aura-services
For the ConfigMap modification, use the following examples for atria-model-gateway, atria-rag-server, aura-gateway-api and aura-services respectively:
kubectl edit configmap atria-model-gw-config -n <namespace>kubectl edit configmap atria-rag-config -n <namespace>kubectl edit cm aura-gateway-api -n <namespace>kubectl edit vs aura-services -n <namespace>
(Substitute <namespace> with the corresponding environment)
You can also use visual tools for this modification, such as Lens or Sublime.
Access to Azure container
You must have access to Azure container atria-resources.
Create ConfigMap copy
Important: Before modifying anything, it is highly recommended to make a backup of the ConfigMap content, as the format is very sensitive
To avoid possible errors, the first thing to do is to copy the current configuration. For this purpose, execute the following commands:
kubectl get cm atria-model-gw-config -o yaml -n <namespace> > <local_file_path>/model-gw-config.yamlkubectl get cm atria-rag-config -o yaml -n <namespace> > <local_file_path>/rag-config.yamlkubectl get cm aura-gateway-api -o yaml -n <namespace> > <local_file_path>/gateway-config.yamlkubectl get vs aura-services -o yaml -n <namespace> > <local_file_path>/services-config.yaml
Change the namespace by the specific one; change local_file_path by the desired path.
Now you have a copy of the current configuration on your local machine.
Create a new preset in atria-model-gateway
Follow these guidelines for adding a new preset in a specific environment through the edition of the ConfigMap of the component:
- Open the ConfigMap atria-model-gw-config
kubectl edit configmap atria-model-gw-config -n <namespace>
(Change<namespace>by the specific one)
Warning: If the presets.yml key is wrongly formatted as a single string, it is necessary to launch the command:
kubectl get cm atria-model-gw-config -n <namespace> -o jsonpath='{.data.presets.yml}'
Afterwards, copy the output and overwrite the whole presets.yml key. This way, you can see the content correctly and include the new preset.
- In the key presets, add the new preset with the following structure:
- id: copilot-reduced-preset-rag model_id: atria-rag name: Copilot group: enriched_ai description: A RAG system built on a LangChain backend session_params: window: 0 preset_params: chain: project-copilot-reduced model_params: max_ref: 3 sticky_context: null candidates_post_filtering: null language: en max_tokens: 16384
Adjust model params
We also have to set the model that the RAG will use to call the atria-model-gateway. This model is the gpt-4o.
-
Open the ConfigMap atria-model-gw-config
kubectl edit configmap atria-model-gw-config -n <namespace>
(Change<namespace>by the specific one) -
Within the key models, search gpt-4o and update the timeout value:
timeout: timeout: 240 read: 240 -
Within the key models, search atria-rag and update the timeout value:
timeout: 485 -
Save and close the ConfigMap
Allow Preset Access
Now that we have created the new preset, we have to modify the access key, to allow the application to use it.
- Within the
accesskey, look for thepresetskey. - In the key 3e1cb831-d5bf-423d-8bef-4abcc53dfa97 (application ID), add the preset name copilot-reduced-preset-rag to the list.
Add a new project in atria-rag-server
Follow these guidelines for adding a new project in a specific environment through the edition of the ConfigMap of the component:
- Open the ConfigMap atria-rag-config
kubectl edit configmap atria-rag-config -n <namespace>
(Change<namespace>by the specific one)
Warning: If the projects.yaml.project key is wrongly formatted as a single string, it is necessary to launch the following command:
kubectl get cm atria-rag-config -n <namespace> -o jsonpath='{.data.projects\.yaml\.project}'
Afterwards, copy the output and overwrite the whole projects.yaml.project key. This way, you can see the content correctly and include the new project.
-
In the key projects.yaml.project, add the new project, as shown below.
Project structure
project-copilot-reduced: name: Project Copilot docs: json: dir: /opt/atria-rag/data/project-copilot-reduced/jsonl extensions: jsonl loader: jsonl embeddings: test_distilbert llm: copilot-rag-model-gw-raw-gpt-4-o solve_type: sql retrievers: qdrant: host: qdrant.aura-system port: 6333 collection_name: project-copilot-reduced-Aura prefix: es-pre-970 tfidf: dump_name: /var/atria-rag-data/tfidf/dump/project-copilot-reduced-Aura serving: base_url: project-copilot-reduced/jsonl parameters: candidate_only: false prompts: generate_sql_query: DEFAULT: | Generate a SQL query statement to answer the following question: `{question}` Use the data contained in the following tables. {sql_table_definition} The following tables, containing auxiliary information, are also available. They include **dimensional tables**: ```sql CREATE TABLE D_CBD_Static_Geo_Area_v6 (GEO_AREA_ID VARCHAR, CBD_GEO_AREA_LEVEL1_ID VARCHAR, CBD_GEO_AREA_LEVEL2_ID VARCHAR, CBD_GEO_AREA_LEVEL3_ID VARCHAR, CBD_GEO_AREA_LEVEL4_ID VARCHAR, OB_ALPHA_ID VARCHAR, EXTRACTION_TM VARCHAR); COMMENT ON TABLE D_CBD_Static_Geo_Area IS 'Geographical areas. This table contains foreign keys to the different levels of geographical areas. In particular, it contains the foreign keys to these tables: CBD_Static_Geo_Area_Level1, CBD_Static_Geo_Area_Level2, CBD_Static_Geo_Area_Level3, CBD_Static_Geo_Area_Level4. Therefore, this tables is used, via JOIN, to query the geographical information contained in the different levels of geographical areas. For instance, if you have a table T with a field GEO_AREA_ID and you need to check whether this location corresponds to the region of Asturias you will need to look for GEO_AREA_ID in this table, then extract the CBD_GEO_AREA_LEVEL4_ID and query the table CBD_Static_Geo_Area_Level4 to get the name of the region.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area.GEO_AREA_ID IS 'Description: Identifier of the geographical area assigned to the customer (typically the geographical area of the customer home). This identifier is a string code which values are defined in ''D_Geographical_Area'' entity. Format: alphanumeric string. Example values: ''2800983CE'', ''50059'', ''3101142CE'''; COMMENT ON COLUMN D_CBD_Static_Geo_Area.CBD_GEO_AREA_LEVEL1_ID IS 'Identifier of the geographical area Level 1 (max level of detail: CP or similar). FORMAT: string containing a numerical code. This field does not contain location names.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area.CBD_GEO_AREA_LEVEL2_ID IS 'Identifier of the geographical area Level 2 (City/Town). FORMAT: string containing a numerical code. This field does not contain location names.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area.CBD_GEO_AREA_LEVEL3_ID IS 'Identifier of the geographical area Level 3 (Province). FORMAT: string containing a numerical code. This field does not contain location names.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area.CBD_GEO_AREA_LEVEL4_ID IS 'Identifier of the geographical area Level 4 (State/Region). FORMAT: string containing a numerical code. This field does not contain location names.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area.OB_ALPHA_ID IS 'Alphanumeric Organizational Business ID'; COMMENT ON COLUMN D_CBD_Static_Geo_Area.EXTRACTION_TM IS 'Date-time of the record'; CREATE TABLE D_CBD_Static_Geo_Area_Level2_v6 (CBD_GEO_AREA_LEVEL2_ID VARCHAR, GEO_AREA_LEVEL_DES VARCHAR, CBD_GEO_AREA_LEVEL3_ID VARCHAR, LONGITUDE_LON_CO DOUBLE, LATITUDE_LAT_CO DOUBLE, GEO_AREA_ID VARCHAR, GEO_STD_AREA_CD VARCHAR, OB_ALPHA_ID VARCHAR, EXTRACTION_TM VARCHAR); COMMENT ON TABLE D_CBD_Static_Geo_Area_Level2 IS 'Geographical area level 2 (State)'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.CBD_GEO_AREA_LEVEL2_ID IS 'Identifier of the geographical area Level 2 (City/Town). FORMAT: string containing a numerical code.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.GEO_AREA_LEVEL_DES IS 'Description associated to the identifier level 2. FORMAT: alphanumeric string containing the name of the city/town.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.CBD_GEO_AREA_LEVEL3_ID IS 'Identifier of the geographical area Level 3 (Province)'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.LONGITUDE_LON_CO IS 'Longitude coordinates (in WGS84) associated with level 2'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.LATITUDE_LAT_CO IS 'Latitude coordinates (in WGS84) associated with level 2'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.GEO_AREA_ID IS 'Description: Identifier of the geographical area assigned to the customer (typically the geographical area of the customer home). This identifier is a string code which values are defined in ''D_Geographical_Area'' entity. Format: alphanumeric string. Example values: ''2800983CE'', ''50059'', ''3101142CE'''; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.GEO_STD_AREA_CD IS 'Standard code of the geo area'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.OB_ALPHA_ID IS 'Alphanumeric Organizational Business ID'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level2.EXTRACTION_TM IS 'Date-time of the record'; CREATE TABLE D_CBD_Static_Geo_Area_Level3_v6 (CBD_GEO_AREA_LEVEL3_ID VARCHAR, GEO_AREA_LEVEL_DES VARCHAR, CBD_GEO_AREA_LEVEL4_ID VARCHAR, LONGITUDE_LON_CO DOUBLE, LATITUDE_LAT_CO DOUBLE, ISO_3166_2_CD VARCHAR, GEO_AREA_ID VARCHAR, GEO_STD_AREA_CD VARCHAR, OB_ALPHA_ID VARCHAR, EXTRACTION_TM VARCHAR); COMMENT ON TABLE D_CBD_Static_Geo_Area_Level3 IS 'Geographical area level 3 (Region)'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.CBD_GEO_AREA_LEVEL3_ID IS 'Identifier of the geographical area Level 3 (Province). FORMAT: string containing a numerical code.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.GEO_AREA_LEVEL_DES IS 'Description associated to the identifier level 3. FORMAT: alphanumeric string containing the name of the province.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.CBD_GEO_AREA_LEVEL4_ID IS 'Identifier of the geographical area Level 4 (State/Region). FORMAT: string containing a numerical code.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.LONGITUDE_LON_CO IS 'Longitude coordinates (in WGS84) associated with level 3'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.LATITUDE_LAT_CO IS 'Latitude coordinates (in WGS84) associated with level 3'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.ISO_3166_2_CD IS 'ISO 3166-2 associated'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.GEO_AREA_ID IS 'Description: Identifier of the geographical area assigned to the customer (typically the geographical area of the customer home). This identifier is a string code which values are defined in ''D_Geographical_Area'' entity. Format: alphanumeric string. Example values: ''2800983CE'', ''50059'', ''3101142CE'''; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.GEO_STD_AREA_CD IS 'Standard code of the geo area'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.OB_ALPHA_ID IS 'Alphanumeric Organizational Business ID'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level3.EXTRACTION_TM IS 'Date-time of the record'; CREATE TABLE D_CBD_Static_Geo_Area_Level4_v6 (CBD_GEO_AREA_LEVEL4_ID VARCHAR, GEO_AREA_LEVEL_DES VARCHAR, LONGITUDE_LON_CO DOUBLE, LATITUDE_LAT_CO DOUBLE, HASC_1_CD VARCHAR, GEO_AREA_ID VARCHAR, GEO_STD_AREA_CD VARCHAR, OB_ALPHA_ID VARCHAR, EXTRACTION_TM VARCHAR); COMMENT ON TABLE D_CBD_Static_Geo_Area_Level4 IS 'Geographical area level 4 (min. Detail)'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.CBD_GEO_AREA_LEVEL4_ID IS 'Identifier of the geographical area Level 4 (State/Region). FORMAT: string containing a numerical code.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.GEO_AREA_LEVEL_DES IS 'Description associated to the identifier level 4. FORMAT: alphanumerical string containing the name of the state/region. EXAMPLE VALUES: ''Asturias'', ''Andaluc\u00eda'', etc.'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.LONGITUDE_LON_CO IS 'Longitude coordinates (in WGS84) associated with level 4'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.LATITUDE_LAT_CO IS 'Latitude coordinates (in WGS84) associated with level 4'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.HASC_1_CD IS 'Hierarchical administrative subdivision codes '; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.GEO_AREA_ID IS 'Description: Identifier of the geographical area assigned to the customer (typically the geographical area of the customer home). This identifier is a string code which values are defined in ''D_Geographical_Area'' entity. Format: alphanumeric string. Example values: ''2800983CE'', ''50059'', ''3101142CE'''; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.GEO_STD_AREA_CD IS 'Standard code of the geo area'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.OB_ALPHA_ID IS 'Alphanumeric Organizational Business ID'; COMMENT ON COLUMN D_CBD_Static_Geo_Area_Level4.EXTRACTION_TM IS 'Date-time of the record'; CREATE TABLE D_CBD_Static_Station_Type_v6 (STATION_TYPE_CD VARCHAR, TECH_LEVEL_WEIGHT_QT FLOAT, STATION_TYPE_L2_DES VARCHAR, STATION_TYPE_L1_DES VARCHAR, STATION_TYPE_L2_ORDER_NUM INT, STATION_TYPE_L1_ORDER_NUM INT, STATION_TYPE_ORDER_NUM INT, CONSCIOUS_IND BOOLEAN, EXTRACTION_TM VARCHAR); COMMENT ON TABLE D_CBD_Static_Station_Type IS 'Station types'; COMMENT ON COLUMN D_CBD_Static_Station_Type.STATION_TYPE_CD IS 'Description: Type of device connected to the HGU router. It used to find out which devices are connected to routers in households. Format: String. Example values: "A/V Equipment", "Air Conditioning", "Air Conditioning Control", "Apple Handheld Device", "Apple Home Device", "AudioCast", "Audiocast", "Barcode Printer", "Camera", "Car Dash Cam", "Cryptominner", "Digital Clock", "Dishwasher", "Drone Equipment", "GPS", "Gaming Console", "Hyper Media Player", "IP Camera", "IPC Hub", "IPC Video Recorder", "IoT Device", "Key Cutting Machine", "Media Center", "Monitoring Device", "Multimedia Player", "Network Access Point", "Network Equipment", "PC", "PDA", "PIR Sensor", "Print Server", "Printer", "Projector", "Raspberry", "Router", "Security System", "Smart AC Control", "Smart Air Freshener", "Smart Air Fryer", "Smart Air Ventilator", "Smart Animal Feeder", "Smart Baby Monitor", "Smart Blind", "Smart Bulb", "Smart Bulb Adapter", "Smart Car", "Smart Car e-Charger", "Smart Display e-bike", "Smart Energy Analyzer", "Smart Home Controller", "Smart Home Hub", "Smart Humidifier", "Smart Hydrometer Clock", "Smart Kitchen Appliances", "Smart Kitchen Scale", "Smart Lamp", "Smart Light Dimmer", "Smart Lock Control", "Smart Plug", "Smart Pool", "Smart Power Strip", "Smart Purifier", "Smart Scale", "Smart Signage", "Smart Speaker", "Smart Switch", "Smart TV", "Smart Thermostat", "Smart Toothbrush", "Smart Vacuum", "Smart WallSocket", "Smart Watch", "Smart Watch Fit", "Smart WifiButton", "Smartphone", "Smartphone/Tablet", "Smartwatch", "Smartwatch Fit", "Solar Panel Equipment", "Soundbar", "Steam Controller", "Storage Device", "TPV", "TV Dongle", "Tablet", "Tempest Weather System", "UPS", "VR/AR Headset", "Video Doorbell", "Video Intercom", "Video STB Equipment", "VideointercomIP", "Virtual Desktop", "VoIP Phone", "WAN Extender", "WiFi Extender", "Wifi Dongle", "Wireless Blood Pressure Monitor", "Wireless Bridge", "Wireless Headphones", "Wireless Router + VoIP Series", "e-Note", "eBook"'; COMMENT ON COLUMN D_CBD_Static_Station_Type.TECH_LEVEL_WEIGHT_QT IS 'Associated weight for the technologic level of the home'; COMMENT ON COLUMN D_CBD_Static_Station_Type.STATION_TYPE_L2_DES IS 'Description: Higher level device type grouping. Example values: "PCs & Home Office", "Smartphones / Tablets / eReaders / iWatch", "Multimedia Entertainment", "Gaming", "Sport & Health", "Smart Home", "Unknown", "Network Devices", "Security & Control"'; COMMENT ON COLUMN D_CBD_Static_Station_Type.STATION_TYPE_L1_DES IS 'Description: Intermediate level device type grouping. Example values: "Smart Speakers & Audio", "PCs & Home Office", "Video Entertainment", "Domestic Appliances", "Smart Energy & Lighting", "Apple Handheld Device", "Smartphones / Tablets / eReaders", "Gaming", "Sport & Health", "Network Devices", "Security & Control", "IoT"'; COMMENT ON COLUMN D_CBD_Static_Station_Type.STATION_TYPE_L2_ORDER_NUM IS 'Station type order level 2'; COMMENT ON COLUMN D_CBD_Static_Station_Type.STATION_TYPE_L1_ORDER_NUM IS 'Station type order level 1'; COMMENT ON COLUMN D_CBD_Static_Station_Type.STATION_TYPE_ORDER_NUM IS 'Station type order'; COMMENT ON COLUMN D_CBD_Static_Station_Type.CONSCIOUS_IND IS 'Indicates if the related device type has energy efficiency'; COMMENT ON COLUMN D_CBD_Static_Station_Type.EXTRACTION_TM IS 'Date-time of the record'; CREATE TABLE D_Segment_v8 (OPERATOR_ID VARCHAR, SEGMENT_ID VARCHAR, SEGMENT_DES VARCHAR, GBL_SEGMENT_ID VARCHAR, SEGMENT_GROUP_ID VARCHAR, SEGMENT_GROUP_DES VARCHAR, EXTRACTION_TM VARCHAR); COMMENT ON TABLE D_Segment IS 'Classifications of the customers, attending to different segmentation criteria, for marketing and management issues, according to OB criteria and its correspondence with the global segment classification'; COMMENT ON COLUMN D_Segment.OPERATOR_ID IS 'Global Operator Identifier (Operator acting as owner of the information present in the current entity)'; COMMENT ON COLUMN D_Segment.SEGMENT_ID IS 'Description: Organisational segment of the client. Format: two letter string. Possible values: ''NT'' - NTT, ''GP'' - Residencial, ''PE'' - Pymes, ''RE'' - Residencial/SC, ''AU'' - Autonomos, ''OP'' - Operadores, ''GC'' - Grandes Clientes, ''RP'' - Residencial Prepago, ''TE'' - Telefonica, ''SC'' - Sin Clasificar, ''ME'' - Empresas'; COMMENT ON COLUMN D_Segment.SEGMENT_DES IS 'Description: Name or description of the organisational segment of the client (provides the description for each segment identifier). Format: string. Example values: ''Residencial", ''Pymes'', ''Autonomos'', ''Operadores'', ''Grandes Clientes'', ''Sin Clasificar'''; COMMENT ON COLUMN D_Segment.GBL_SEGMENT_ID IS 'ID of the global segment classification'; COMMENT ON COLUMN D_Segment.SEGMENT_GROUP_ID IS 'ID code of the segmentation group'; COMMENT ON COLUMN D_Segment.SEGMENT_GROUP_DES IS 'Description of the segmentation group'; COMMENT ON COLUMN D_Segment.EXTRACTION_TM IS 'Date-time of the record'; CREATE TABLE D_Fixed_Tariff_Plan_v8 (OPERATOR_ID VARCHAR, DAY_DT VARCHAR, TARIFF_PLAN_ID VARCHAR, TARIFF_PLAN_DES VARCHAR, VOICE_IND BOOLEAN, BBAND_IND BOOLEAN, TV_IND BOOLEAN, WORKSTATION_IND BOOLEAN, APP_IND BOOLEAN, VOICE_BUNDLE_QT FLOAT, BBAND_UP_SPEED_QT FLOAT, BBAND_DOWN_SPEED_QT FLOAT, TV_TYPE_CD VARCHAR, FIXED_SERVICE_COMMERCIAL_NAME VARCHAR, COMMERCIAL_IND BOOLEAN, TARIFF_PLAN_START_DT VARCHAR, TARIFF_PLAN_END_DT VARCHAR, CONVERGENT_IND BOOLEAN, BRAND_ID VARCHAR); COMMENT ON TABLE D_Fixed_Tariff_Plan_v8 IS 'Every fixed Tariff to be applied, either Commercial, Convergent, Individual, or any other, for any product&service for the fixed client base'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.OPERATOR_ID IS 'Global Operator Identifier (Operator acting as owner of the information present in the current entity)'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.DAY_DT IS 'Year, month and day of the data ### Additional Information Format: YYYYMMDD (4 digits for year, months from 01 to 12, days from 01 to 31).'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.TARIFF_PLAN_ID IS 'Unique identifier of the tariff plan'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.TARIFF_PLAN_DES IS 'Name/short description of the tariff plan'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.VOICE_IND IS 'Indicates whether the line has a fixed line voice service associated. Values: 0=No; 1=Yes.'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.BBAND_IND IS 'Indicates whether the line has a Broadband service associated. Values: 0=No; 1=Yes.'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.TV_IND IS 'Indicates if the line has a TV service associated. Values: 0=No; 1=Yes.'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.WORKSTATION_IND IS 'Indicates if the line has a workstation service associated. Values: 0=No; 1=Yes.'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.APP_IND IS 'Indicates if the line has the "Aplicateca service" associated. Values: 0=No; 1=Yes.'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.VOICE_BUNDLE_QT IS 'Amount of data associated with the voice bundle'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.BBAND_UP_SPEED_QT IS 'Broadband up speed (Mbps)'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.BBAND_DOWN_SPEED_QT IS 'Broadband down speed (Mbps)'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.TV_TYPE_CD IS 'Type of TV line'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.FIXED_SERVICE_COMMERCIAL_NAME IS 'Commercial name of the service'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.COMMERCIAL_IND IS 'Indicates if TARIFF_PLAN_ID refers to the COMMERCIAL_TARIFF_ID. Fill-in with 1 if TARIFF_PLAN_ID refers to the COMMERCIAL_TARIFF_ID or 0 if it doesn''t 0 = Non commercial tariff 1 = commercial tariff'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.TARIFF_PLAN_START_DT IS 'Start date of the tariff plan validity (that day is the first day when the tariff plan is applicable) ### Additional Information Format: YYYYMMDD (4 digits for year, months from 01 to 12, days from 01 to 31).'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.TARIFF_PLAN_END_DT IS 'End date of the tariff plan validity (that day is the last day when the tariff plan is applicable) ### Additional Information Format: YYYYMMDD (4 digits for year, months from 01 to 12, days from 01 to 31).'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.CONVERGENT_IND IS 'Flag indicating if the current fixed tariff plan can be configured as a "Convergent tariff plan", i. e., a plan with special conditions due to the fact of including at least one Fixed line/service and one Mobile line. 0 = No (the plan can''t be configured as convergent) 1 = Yes (the plan can be configured as convergent)'; COMMENT ON COLUMN D_Fixed_Tariff_Plan_v8.BRAND_ID IS 'Commercial brand identifier. In order to differentiate among different brands in the same OB (e.g. Movistar, O2, Tuenti...)'; ``` Some of the former tables contain columns in full-qualified format. For instance, these are some examples of full-qualified columns: ``` <record_name>.<field_name>: TEC_PLAT_REC.DEVICE_ID <record_name>.<subrecord_name>.<field_name>: TEC_PLAT_REC.TEC_PLAT_SUBCOMP_REC.DEVICE_ID ... ``` Always use the full-qualified format when referring to columns in the tables. For instance, if you need to use the column 'TEC_PLAT_REC.DEVICE_ID', you should not refer to it as 'DEVICE_ID', but as 'TEC_PLAT_REC.DEVICE_ID'. **Explain in detail, step by step, all your decisions**. # General instructions ## How to use dimensional tables If you need to filter by a higher level geographical such as a region (Comunidad Autónoma) you will need to: - join the `GEO_AREA_ID` field of the data table (such as `CBD_HGU_Detail_Daily_v10`) with the `GEO_AREA_ID` field in `D_CBD_Static_Geo_Area_v6` table - then join the `CBD_GEO_AREA_LEVEL4_ID` field in the `D_CBD_Static_Geo_Area_v6` with the `CBD_GEO_AREA_LEVEL4_ID` field in the `D_CBD_Static_Geo_Area_Level4_v6` table - then compare the `GEO_AREA_LEVEL_DES` field in the `D_CBD_Static_Geo_Area_Level4_v6` table with the name of the region (e.g., 'Cantabria'), since the DESCRIPTION field does contain the actual name of the geographical area. **Only perform these joins if explicit filtering or grouping by geographical location is necessary**. If you need to filter the `CBD_Summary_HGU_Stations_Daily` table by a period of time yo will need to: - join the `DEVICE_ID` field of the data table (such as `CBD_Summary_HGU_Detail_Daily`) with the `DEVICE_ID` field in `CBD_Summary_HGU_Stations_Daily` table - then join the `DAY_DT` field of the data table (such as `CBD_Summary_HGU_Detail_Daily`) with the `DAY_DT` field in `CBD_Summary_HGU_Stations_Daily` table **Only perform these joins if explicit filtering or grouping by detailed information at a station & interface level is necessary**. Use other dimensional tables in a similar way, if necessary. ## SQL query generation steps Follow these reasoning steps to generate the SQL query: - Step 1: Identify Necessary Tables - Step 2: Identify Useful Candidate Columns - Step 3: Assess if Tables and Columns are Sufficient to Answer the Question - Step 4: Plan the SQL Query - Step 5: Write the final SQL Query and apply the rules - Step 6: Check that the query actually can answer the question - Step 7: Create the result as a JSON object # Detailed instructions ### Step 1: Identify Necessary Tables for answering the question `{question}` First, identify which tables are necessary to answer the question `{question}`. Justify why you selected each of these tables. Use the following format: ``` I need the following tables to answer the question: - <table_name>: <reasoning> - <table_name>: <reasoning> ... ``` ### Step 2: Identify Useful Candidate Columns for answering the question `{question}` Identify which columns are useful to answer the question `{question}`. Justify why you selected each of these columns. Always include any column you think may be needed to answer the question. If there are similar columns in the table, you should identify all of them always. You will later choose which them are more suitable to answer the question. But, at this stage, you should include **all the columns that may be useful**. Write the list of candidate columns you have identified and the reasoning after each column, using the following format: ``` I can use the following candidate columns to answer the question (including all the columns that may be useful): - <table name>: - <column_name>: <copy here the full column description from schema, including possible values if present>: <reasoning>. - <column_name>: <copy here the full column description from schema, including possible values if present>: <reasoning>. ... - <table_name>: - <column_name>: <copy here the full column description from schema, including possible values if present>: <reasoning>. - <column_name>: <copy here the full column description from schema, including possible values if present>: <reasoning>. ... ... ``` ### Step 3: Assess if Tables and Columns are Sufficient to Answer the Question for answering the question `{question}` Tell if the tables and columns you identified are enough to answer the question `{question}`. Make sure to justify your answer and check the actual descriptions of the columns in the table definitions and the user question. Write the answer using the following format: ``` Possible to answer the question using the former columns: - <reasoning> - Result: <Yes|No> ``` ### Step 4: Plan the SQL Query for answering the question `{question}` Explain, step by step, how you would write the SQL query to answer the question `{question}`, using the columns you identified. **Use the full qualified names of the columns**. **DO NOT USE THE `JSON_OBJECT` FUNCTION IN THE QUERY**. To finish this step, explain how you would write the SQL query to answer the question, using the columns you identified, taking into account the previous considerations for columns contained in maps, if there are any. ### Step 5: Write the final SQL Query and apply the rules for answering the question `{question}` Finally, write the SQL query to answer the question `{question}`, using the columns you identified. Remarks: **DO NOT USE THE `JSON_OBJECT` FUNCTION IN THE QUERY**. Check if you need to use any of the following **business rules** to build the query: ```json {{ "rules": [ {{ "id": "B1", "name": "Fiction", "condition": "Look for tariff plans including \"ficción\" contents in the question `{{question}}`.\n", "action": "You will need to look for one the following patterns in the `TARIFF_PLAN_DES` field: '%FICCION%', '%FICCIÓN%', '%SERIES%', '%CINE%', '%FUSIÓN TOTAL%', '%FUSION TOTAL%'. To make the proper comparison, you should use compare with uppercase letters. For instance, use a filter like this one: `UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FICCION%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FICCIÓN%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%SERIES%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%CINE%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FUSIÓN TOTAL%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FUSION TOTAL%'`\n" }}, {{ "id": "B2", "name": "Disney", "condition": "Look for tariff plans including \"Disney\" contents in the question `{{question}}`.\n", "action": "You will need to look for one the following patterns in the `TARIFF_PLAN_DES` field: '%DISNEY%'. To make the proper comparison, you should use compare with uppercase letters. For instance, use a filter like this one: `UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%DISNEY%'`\n" }}, {{ "id": "B3", "name": "Football", "condition": "Look for tariff plans including football contents in the question `{{question}}`.\n", "action": "You will need to look for one the following patterns in the `TARIFF_PLAN_DES` field: '%FUTBOL%', '%FÚTBOL%', '%FUSION TOTAL%', '%FUSIÓN TOTAL%', '%FUSION TA TOTAL%', '%FUSIÓN TA TOTAL%', '%LIGA%', '%CHAMPION%'. To make the proper comparison, you should use compare with uppercase letters. For instance, use a filter like this one: `UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FUTBOL%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FÚTBOL%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FUSION TOTAL%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%FUSIÓN TOTAL%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%LIGA%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%CHAMPION%'`\n" }}, {{ "id": "B4", "name": "Netflix", "condition": "Look for tariff plans including \"Netflix\" contents in the question `{{question}}`.\n", "action": "You will need to look for one the following patterns in the `TARIFF_PLAN_DES` field: '%NETFLIX%', '%FICCIÓN%', '%FICCION%'. To make the proper comparison, you should use compare with uppercase letters. For instance, use a filter like this one: `UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%NETFLIX%'`\n" }}, {{ "id": "B5", "name": "Promociones", "condition": "Need to look for tariff plans including \"promotions\" in the question `{{question}}`.\n", "action": "You will need to look for one the following patterns in the `TARIFF_PLAN_DES` field: '%PROMO%'. To make the proper comparison, you should use compare with uppercase letters. For instance, use a filter like this one: `UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%PROMO%'`\n" }}, {{ "id": "B6", "name": "Edad promedio 1", "condition": "You are using the field `CBD_INFO_REC.CUST_AGE_NUM` in the query.\n", "action": "You are not allowed to use the field `CBD_INFO_REC.CUST_AGE_NUM` in any query. You should use the field `CBD_INFO_REC.CUST_AGE_SEGMENT_CD` instead.\n" }}, {{ "id": "B7", "name": "Edad promedio 2", "condition": "Calculate the average age of customers.\n", "action": "You should use the following calculation instead of AVG(CBD_INFO_REC.CUST_AGE_SEGMENT_CD): AVG(IF(CBD_INFO_REC.CUST_AGE_SEGMENT_CD = '1', NULL, CBD_INFO_REC.CUST_AGE_SEGMENT_CD))\n" }}, {{ "id": "B8.1", "name": "Query by customers", "condition": "The question `{{question}}` is about customers.\n", "action": "You should use the `CUSTOMER_ID` field to filter by customers.\n" }}, {{ "id": "B8.2", "name": "Query by homes", "condition": "The question `{{question}}` is about homes.\n", "action": "You should use the `DEVICE_ID` field to filter by homes.\n" }}, {{ "id": "B9", "name": "Station type", "condition": "The field `STATION_TYPE_L1` or `STATION_TYPE_L2` are used in the query.\n", "action": "The field `STATION_TYPE_L2` corresponds to a higher aggregation level than `STATION_TYPE_L1`. `STATION_TYPE_L1` corresponds to an intermediate category, used only with analytical purposes.\n" }}, {{ "id": "B10.1", "name": "Computing of homes or devices (devices are also known as homes)", "condition": "Check if the question: `{{question}}` is asking for a computation on devices or homes (devices are also known as homes).\n", "action": "If no other condition is set, Include this constraint in the query: `DEVICE_INFO_REC.INACTIVITY_DEVICE_INFO_NUM < 24` (The device must be idle less than 24 hours)\n" }}, {{ "id": "B10.2", "name": "Computing of RSSI", "condition": "Check if the question: `{{question}}` is asking for a computation on RSSI\n", "action": "If no other condition is set, Include this constraint in the query: `DEVICE_INFO_REC.INACTIVITY_DEVICE_INFO_NUM < 24` (The device must be idle less than 24 hours)\n" }}, {{ "id": "B10.3", "name": "Computing of symmetrical speed", "condition": "Check if the question: `{{question}}` is asking for a computation on symmetrical speed\n", "action": "If no other condition is set, Include this constraint in the query: `DEVICE_INFO_REC.INACTIVITY_DEVICE_INFO_NUM < 24` (The device must be idle less than 24 hours)\n" }}, {{ "id": "B11", "name": "Penetración de un producto", "condition": "You are asked for calculating \"la penetración de un producto\" in the question `{{question}}`.\n", "action": "You should calculate the percentage of customers with that product.\n" }}, {{ "id": "B12", "name": "Obsolete routers", "condition": "You are asked for obsolete routers in the question `{{question}}`.\n", "action": "You should check for those with MANUFACT_HGU_CHIPSET_DES IN ('Askey Broadcom', 'Askey Econet','MitraStar Broadcom', 'MitraStar Econet').\n" }}, {{ "id": "B13", "name": "High value customers", "condition": "You are asked for high value customers in the question `{{question}}`.\n", "action": "Consider as high value customers those with a monthly revenue higher than 100 (TOTAL_CUST_RV > 100).\n" }}, {{ "id": "B14.1", "name": "Technological level formula", "condition": "Check the technological level of a customer in the question `{{question}}`.\n", "action": "Use the following formula on the field `TECH_LEVEL_WEIGHT_QT` of the table `D_CBD_STATIC_STATION_TYPE_v6`: `SUM(COALESCE(D_CBD_STATIC_STATION_TYPE_v6.TECH_LEVEL_WEIGHT_QT,0) + CASE WHEN AMM.VALUE.STATION_BRAND_DES = 'Ubiquiti' THEN 0.8 ELSE 0 END)/COUNT(DISTINCT DAY_DT)`\n" }}, {{ "id": "B14.2", "name": "Technological levels", "condition": "You are asked for the technological level of a customer in the question `{{question}}`.\n", "action": "Consider as **high technological level** customers those with a value higher or equal to 2.5. Consider as **medium technological level** customers those with a value higher or equal to 1 and lower than 2.5. Consider as **low technological level** customers those with a value lower than 1.\n" }}, {{ "id": "B15", "name": "Sport", "condition": "Look for tariff plans including \"sport\" contents.\n", "action": "You will need to look for one the following patterns in the `TARIFF_PLAN_DES` field: '%DEPORTE%', '%TOTAL PLUS%', '%TOTAL SAT%PLUS%', '%MOTOR%', '%DAZN%'. To make the proper comparison, you should use compare with uppercase letters. For instance, use a filter like this one: `(UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%DEPORTE%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%TOTAL PLUS%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%TOTAL SAT%PLUS%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%MOTOR%' OR UPPER(${{{{TABLE}}}}.TARIFF_PLAN_DES) LIKE '%DAZN%')`\n" }}, {{ "id": "B16", "name": "Residencial", "condition": "The question `{{question}}` asks for homes or residential customers (B2C users).\n", "action": "Use ONLY the constraint:`CBD_INFO_REC.SEGMENT_ID = 'GP'`. If you use the constraint: `SEGMENT_DES = 'Residencial', NEVER USE the value in English ('Residential') but the value in Spanish ('Residencial').\n" }}, {{ "id": "R1", "name": "Temporary table fields", "condition": "You use in a filter a given filed from a temporary table, built using the `WITH` clause.\n", "action": "Make sure that the field is actually present in the SELECT statement defining the temporary table.\n" }}, {{ "id": "R2", "name": "Temporary table field naming", "condition": "You write a temporary table like this: `WITH temp_table AS (SELECT field1_prefix.field1 FROM table)`.\n", "action": "then you should use refer to the field as `field1` and not as `field1_prefix.field1` in the rest of the query.\n" }}, {{ "id": "R3", "name": "Tariff plan", "condition": "Look for some specific tariffs in the question `{{question}}`.\n", "action": "Use the field `TARIFF_PLAN_DES` from the dimensional table D_Fixed_Tariff_Plan instead of using `CBD_INFO_REC.COMMERCIAL_TARIFF_ID` since this last one only contains identifiers without any meaning.\n" }}, {{ "id": "R4.1", "name": "Station type 1", "condition": "The query uses `D_CBD_Static_Station_Type_v6.STATION_TYPE_L1_DES` or `D_CBD_Static_Station_Type_v6.STATION_TYPE_L2_DES`.\n", "action": "Answer this question: does the value you are looking for match one of the possible values of these fields? Justify your answer. Enumerate the possible values of these fields if they are used.\n" }}, {{ "id": "R4.2", "name": "Station type 2", "condition": "The query uses a filter with the field `D_CBD_Static_Station_Type_v6.STATION_TYPE_L1_DES` or `D_CBD_Static_Station_Type_v6.STATION_TYPE_L2_DES` and the value you are looking for does not match any of the possible values of these fields.\n", "action": "You should use the field `STATION_TYPE_CD` instead. Write the result of the previous reasoning in detail. REMEMBER TO FIX THE QUERY TO USE THE FIELD `STATION_TYPE_CD` INSTEAD.\n" }}, {{ "id": "R5", "name": "Counting entities", "condition": "Count the number of customers, homes, devices or any other entities in the question `{{question}}`.\n", "action": "You should ensure that you are actually counting distinct entities. Therefore you should use the `COUNT(DISTINCT ...)` function instead of `COUNT(...)`.\n" }}, {{ "id": "R6", "name": "Time scope less than a month", "condition": "You are asked to answer a question for a time scope minor than a month (daily or weekly) in the question `{{question}}`.\n", "action": "you must not use the field `MONTH_DT` in your query.\n" }}, {{ "id": "R7", "name": "No UNION operator", "condition": "You use the UNION operator in your queries.\n", "action": "Avoid using the UNION operator in your queries.\n" }}, {{ "id": "R8", "name": "Counting entities", "condition": "You are asked to count the number of customers, homes, devices or any other entities in the question `{{question}}`.\n", "action": "You should ensure that the result is actually a count and not a list of elements. Therefore you should use the COUNT function.\n" }}, {{ "id": "R9", "name": "IoT devices", "condition": "Look for IoT (Internet of Things) devices in the question `{{question}}`.\n", "action": "You should look for devices with `STATION_TYPE_L2_DES = 'Smart Home'`\n" }}, {{ "id": "R10", "name": "Router model", "condition": "Check the model of the router in the question `{{question}}`.\n", "action": "You should use the field `MANUFACT_HGU_CHIPSET_DES` (do not use other fields such as `MANUFACTURER_FW_VER_DES`).\n" }}, {{ "id": "R11", "name": "Weekly period", "condition": "Query data from weekly period.\n", "action": "You should start always with the specified day up to the same day of the following week. For instance, if you are asked for the week starting on the day 2022-01-01, you should query data from 2022-01-01 to 2022-01-07.\n" }}, {{ "id": "R12", "name": "WiFi type", "condition": "Look for information on a specific WiFi type, such as 2.4 GHz or 5 GHz.\n", "action": "You should use the specific fields corresponding to these types. For instance, if you need to look for WiFi5 device information, you should not use the field `STATIONS_REC.WIFI_REC.ALL_TECH_REC` but the field `STATIONS_REC.WIFI_REC.TECH_5G_REC`.\n" }}, {{ "id": "R13", "name": "Equivalent terms for WiFi technologies", "condition": "You are looking for information on WiFi technologies.\n", "action": "The following terms are considered equivalent: \n- `WiFi 5G`, `WiFi Technology 5G`, `WiFi5`.\n- `WiFi 2.4G`, `WiFi Technology 2.4G`, `WiFi2.4` , `WiFi2`, `WiFi Technology 2G`, `WiFi 2G`.\n" }}, {{ "id": "R14", "name": "Customer Satisfaction Index", "condition": "The query uses the field `CSI_QT`.\n", "action": "You should keep in mind that the field `CSI_QT` contains the `Customer Satisfaction Index` value. It is not a quality value but a satisfaction value. Do not confuse it with Quality Index fields.\n" }}, {{ "id": "R15", "name": "Active HGU devices", "condition": "Look for active HGU devices.\n", "action": "You should keep in mind that the field `CUST_HGU_DEVICES_NUM` contains the number of active HGU devices of the customer, i.e. the number of active routers (HGUs) of the customer. Do not confuse it with the number of active devices of the customer.\n" }}, {{ "id": "R16", "name": "Megabytes", "condition": "The query uses fields starting with `MB_` or containing `_MB_` in their name.\n", "action": "Keep in mind that fields starting with `MB_` or containing `_MB_` in their name refer to Megabytes. Take this into account during your queries.\n" }}, {{ "id": "R17", "name": "Gigabytes", "condition": "The query uses fields starting with `GB_` or containing `_GB_` in their name.\n", "action": "Keep in mind that fields starting with `GB_` or containing `_GB_` in their name refer to Gigabytes. Take this into account during your queries.\n" }}, {{ "id": "R18", "name": "RSSI meaning", "condition": "The query uses the field `RSSI`.\n", "action": "Keep in mind that the field `RSSI` refers to the `Received Signal Strength Indicator`. It is a measure of the power present in a received radio signal.\n" }}, {{ "id": "R19", "name": "Checking absence of a device", "condition": "You need to look for homes without a specific type of device.\n", "action": "You should not forget checking at least one of the following fields: `STATION_TYPE_L1_DES`, `STATION_TYPE_L2_DES`, `STATION_TYPE_CD`. In other words, you need an explicit filter checking the absence of the device.\n" }} ] }} ``` Explain whether you can apply any of the rules and explain how you would apply them in the SQL query. Always write your result following these steps: 5.1. Question to be answered: <write again the question here> 5.2. SQL query: <write the SQL query here> 5.3. Reasoning: <explain why you wrote the query like that> 5.4. Check of the rules, RULE BY RULE and FOR EACH RULE (one entry per rule). Write ALL the rules and tell if they are applied or not. Follow this format: - <rule1>: Should be applied, because <reason> | Should not be applied, because <reason> - <rule2>: Should be applied, because <reason> | Should not be applied, because <reason> ... 5.5. Result of the execution of the rules that have been identified to be applied. Follow this format: - <rule1>: <result> - <rule2>: <result> ... 5.6. Need to fix the query because <reason>. The following changes are needed: <change_1>, <change 2>, etc. | The query is already correct. 5.7. SQL query to answer the question `{question}` after considering the previous **rules**: <write the SQL query here>. FIX THE QUERY IF NECESSARY. Check that the fixed query includes all the rules that should apply. ### Step 6: Check that the query actually can answer the question for answering the question `{question}` Check again if the generated query answers the question `{question}`. Follow these steps: 6.1. Write the concepts involved in the question. Enumerate the concepts as a list. Follow this format: - <concept1> - <concept2> ... 6.2. Write all the concepts of the question that are covered by the SQL query. Enumerate them and create a match list with the concepts from the previous step. Write down the part of the SQL query covering the concept. Take into account that conditions on specific proper names, such as model names, location names, etc., need to be explicitly checked with the description of the corresponding column. Follow this format: - <concept1>: covered in <sql query section> or not covered. - <concept2>: covered in <sql query section> or not covered. 6.3. Find those concepts in the question that are not covered by the SQL query. 6.4. Conclude whether the question can actually be answered by the generated query. Follow this format: - The question can be answered by the SQL query: <Yes|No> ### Step 7: Create the result as a JSON object for answering the question `{question}` Return the result as a unique JSON object, with the following structure: {{ "result": <Write the SQL query here. **MAKE SURE THAT THE STATEMENT `SELECT JSON_OBJECT` is not used in the query and Use the full qualified names of the columns. Generate a valid SQL sentence in a single line without new line characters.**>, "status": "OK", "reason": <a reasoning explaining the query> }} If the former table does not contain the necessary data to answer the question, return the following JSON object: {{ "result": null, "status": "ERROR", "reason": <a reasoning explaining why it is not possible to answer the question> }} Make sure that the JSON object is correctly formatted, and can be parsed by a JSON parser. **Please, ALWAYS follow the 7 steps presented in the instructions.** Start reasoning with ### Step 1 and finish with ### Step 7.
Some considerations to keep in mind:
. Make sure that the LLM copilot-rag-model-gw-raw-gpt-4-o is defined within the LLMs field.
. In turn, the preset defined within this LLM must be defined in the ConfigMap atria-model-gw-config.
- Save and close the ConfigMap
Adjust max_tokens param
-
Open the ConfigMap atria-rag-config
kubectl edit configmap atria-rag-config -n <namespace>
(Change<namespace>by the specific one) -
In
llmskey, search copilot-rag-model-gw-raw-gpt-4-o and update the max_tokens field:max_tokens: 16384 -
Save and close the ConfigMap
Adjust timeouts in aura-gateway-api and Nginx
-
Open the ConfigMap aura-gateway-api
kubectl edit configmap aura-gateway-api -n <namespace>
(Change<namespace>by the specific one) -
In
configkey, search and update the AURA_REQUEST_TIMEOUT field:AURA_REQUEST_TIMEOUT: 490000 -
Save and close the ConfigMap
-
Open the ConfigMap aura-services
kubectl edit vs aura-services -n <namespace>
(Change<namespace>by the specific one) -
In
aura-gateway-apikey, search and update read_timeout and send_timeout field:read_timeout: 495s send_timeout: 495s -
Save and close the ConfigMap
Upload documents and execute generate-db job
Follow the guidelines for uploading new or modified documents in a specific environment through the edition of the ConfigMap of the component (included in the general guidelines Import documents into ATRIA).
- Upload the documents in the Azure container
atria-resources.
- Remember to upload the files to the folder you defined previously in the config project-copilot-reduced/jsonl
- Keep in mind the allowed formats for documents, set in the project’s variable
loader.
- Finally, execute the atria-rag-generate-db job to update the data into the environment.
Restart the deployments
-
Restart atria-rag-server deployment for the pod to be updated with the changes.
kubectl rollout restart deployment atria-rag -n <namespace> -
Restart atria-model-gateway deployment for the pod to be updated with the changes.
kubectl rollout restart deployment atria-model-gw -n <namespace> -
Restart aura-gateway-api deployment for the pod to be updated with the changes.
kubectl rollout restart deployment aura-gateway-api -n <namespace>
(Change <namespace> by the specific one)
Update Aura applications configuration via API
Once the changes have been updated and saved in the ConfigMaps, the aura-configuration-api must be updated to indicate the application that will make use of this preset.
This document includes a specific scenario in the process of modifying API configuration, described in the document Hot swapping of Aura applications configuration.
curl --location --request PATCH 'https://svc-<env>.auracognitive.com/aura-services/v2/configuration/applications/3e1cb831-d5bf-423d-8bef-4abcc53dfa97' \
--header 'correlator: <uuid>' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--header 'Authorization: APIKEY {{apikey}}' \
--data '{
"id": "3e1cb831-d5bf-423d-8bef-4abcc53dfa97",
"models": {
"presets": [
"copilot-preset-rag",
"copilot-reduced-preset-rag",
"raw-gpt-4o",
"openai-preset-gpt-35-turbo-copilot-generative",
"openai-preset-gpt-4o-copilot-generative",
"openai-preset-gpt-4o-mini-copilot-generative"
]
}
}'
It is necessary to send all application presets in the request.
Load original config and deployments rollback
In case you want to return to the original configuration, the following steps must be carried out:
-
Load the original ConfigMap atria-model-gw-config.
kubectl apply -f <local_file_path>/model-gw-config.yaml -n <namespace> -
Load the original ConfigMap atria-rag-config.
kubectl apply -f <local_file_path>/rag-config.yaml -n <namespace> -
Load the original ConfigMap aura-gateway-api.
kubectl apply -f <local_file_path>/gateway-config.yaml -n <namespace> -
Load the original ConfigMap aura-services.
kubectl apply -f <local_file_path>/services-config.yaml -n <namespace>
(Change <namespace> by the specific one; change local_file_path by the desired path)
-
Restart atria-model-gateway deployment for the pod to be updated with the changes.
kubectl rollout restart deployment atria-model-gw -n <namespace> -
Restart atria-rag-server deployment for the pod to be updated with the changes.
kubectl rollout restart deployment atria-rag -n <namespace> -
Restart aura-gateway-api deployment for the pod to be updated with the changes.
kubectl rollout restart deployment aura-gateway-api -n <namespace>
(Change <namespace> by the specific one)
Last modified September 4, 2025: feat: Next steps for agents platform. #AURA-29924 [RTM] (60eae014)