Stored Procedures

Lyftrondata stored procedures

Apart from emulating SQL Server's stored procedures, Lyftrondata introduces some of its own. All of them start with lyft_ prefix to be easily distinguished from regular SQL Server stored procedures.

Administrative

Administrative stored procedures should be used by power users and administrators. Note that most of the tasks can also be achieved conveniently using Lyftrondata Admin Portal user interface.

Stored procedure name	Description
lyft_flush_plan_cache	Flushes the whole execution plan cache.
lyft_run_garbage_collection	Attempts garbage collection to reclaim unused memory.
lyft_set_default_federator	Configures federator for an agent. Given connection will be the new default federator for the designated agent.

 

Cache Management

Stored procedure name	Description
lyft_create_view_copy	Creates a primary/secondary view copy for the materialized view.
lyft_evict_spark_inmem_cached_table	Removes data from Spark in-memory cache for given table.
lyft_full_load_cached_view_copy	Loads cache for a given view.
lyft_full_load_view_copy	Fills a primary/secondary view copy.
lyft_incremental_insert	Inserts new rows into an incrementally cached view.
lyft_incremental_load_cached_view_copy	Conducts the whole process of an incremental load of new rows.
lyft_invalidate_spark_cached_table	Invalidates and then loads Spark in-memory cache for given table.
lyft_populate_cached_table	Materializes a cached view.
lyft_refresh_view
lyft_set_reload_mode	Sets reload mode for given view.
lyft_set_view_cache_mode	Sets the cache mode for given view.
lyft_set_view_copy_status
lyft_set_incremental_marker	Sets new incremental marker value for given table or view.
lyft_set_incremental_marker_column	Sets incremental marker column for given table or view. The column will be later used for incremental load process.
lyft_switch_view_copy	Switches view copy between the primary and secondary table for cached views with two rotated tables.
lyft_trim_view_copy	Drops persistent cache and recreates it.

 

Connection Management

Stored procedure name	Description
sp_addlinkedserver	Creates a connection to a data source. A connection allows for access to a single data source (endpoint).
sp_addlinkedsrvlogin	Creates or updates a mapping between a connection on the local instance of Lyftrondata and a security account on a remote data source.
sp_dropserver	Removes a connection from the list of known connections on the instance of Lyftrondata.
sp_testlinkedserver	Tests the connection to a data source.
sp_updatelinkedserver	Updates a connection to a data source.

 

Diagnostics

Stored procedure name	Description
lyft_set_logging_level	Dynamically changes Lyftrondata logging level.
lyft_help_jobs	Fetches a data set with details of all jobs that the schedule is aware of.

 

Job Management

Stored procedure name	Description
sp_add_job	Adds a job executed by the job scheduler.
sp_add_jobstep	Adds a step (operation) to a job.
sp_add_schedule	Creates a schedule that can be used by any number of jobs.
sp_attach_schedule	Sets a schedule for a job.
sp_detach_schedule	Removes an association between a schedule and a job.
sp_delete_job	Deletes a job.
sp_start_job	Instructs the scheduler to execute a job immediately.
sp_update_job	Changes the attributes of a job.
sp_update_jobstep	Changes the setting for a step in a job that is used to perform automated activities.
sp_update_schedule	Changes the settings for a schedule.
sp_delete_jobstep	Deleted a step from the job.
sp_delete_schedule	Deletes a schedule.

 

Utility

Stored procedure name	Description
lyft_execute_provider_sql_on_connection	Executes provider-specific SQL query or command on a given connection.
lyft_update_object_statistics	Updates statistics (rowcount and distinct count) for a given table.
lyft_update_row_count_object_statistics	Updates rowcount statistics for a given table.

 

Metadata Management

Stored procedure name	Description
lyft_get_partitioning_schema	Retrieves partitioning schema for a given view.
lyft_set_partitioning_schema	Sets partitioning mode and schema for a given view.
sp_generate_views	Generates views on target database based on objects (tables or views) from a source database.
sp_import_metadata	Generates views on target database based on objects (tables or views) from a source database.

 

JSON /XML Parsing

Stored procedure name	Description
lyft_detect_xml_paths	This procedure accepts a select statement as a parameter, runs the query and analyzes the first statement and detects unique xml paths for xml columns returned as the first output column of the query.
lyft_detect_xml_value	This function helps in converting XML into relational format by preparing a relational format with values as an output. It will detect XML type data for the first column in the provided Sql statement and convert into XPaths which are further evaluated by query analyzer. The schema is built from the table having highest value of XML data and returns the row from the table having highest XML data.
lyft_detect_xml_sql	This function helps in automatically converting XML into relational format by preparing sql with XML parsing.  It will detect XML type data for the first column in the provided Sql statement and convert into XPaths which is further consolidated in an Sql Select statement which is returned as an output to the user. Users can then use this statement to query the data from that specific table.
lyft_detect_json_value	This procedure accepts a select statement as a parameter, runs the query and analyzes the first statement and detects unique Json value for json columns returned as the first output column of the query.
lyft_detect_json_paths	This function helps in converting provided JSON into Json paths. It will detect JSON type data for the first column in the provided Sql statement and convert into Json paths which is then output to the user in relational format. The schema is built from the table having highest value of JSON data and returns the row from the table having highest JSON data.
lyft_detect_json_sql	This function helps in automatically converting Json into relational format by preparing sql with Json field parsing.  It will detect JSON type data for the first column in the provided Sql statement and convert into Json paths which is further consolidated in an Sql Select statement which is returned as an output to the user. Users can then use this statement to query the data from that specific table.

 

Spark Management

Stored procedure name	Description
lyft_spark_force_become_active	Transitions the node to Active, regardless of Zookeeper state.
lyft_spark_force_become_standby	Transitions the node to StandBy, regardless of Zookeeper state.
lyft_spark_instance_kill	Forces an OS-level kill on the given Spark process.
lyft_spark_instance_start	Tries to find a running Spark process in the OS. If not found, a new process is started.
lyft_spark_instance_stop	Sends a stop-server signal to the remote driver.
lyft_spark_release	Releases Spark context and transitions driver to non-initialized state.
lyft_spark_stop_driver	Sends a stop-server signal to the remote driver.

 

System Views

Lyftrondata-specific views

Apart from emulating SQL Server's views, Lyftrondata introduces some of its own. All of them start with lyft_ prefix to be easily distinguished from regular SQL Server views.

In general, these views should be used by software developers and administrators. Note that most of the tasks can also be achieved conveniently using Lyftrondata Admin Portal user interface.

View name	Description
lyft_functions	Provides list of functions supported by each Lyftrondata provider.
lyft_materialized_views	Contains rows about each materialized view (cached views) and their physical counterparts, that are used as a backend storage of the view.
lyft_provider_tables	Provides all tables that exist on a provider connection for a specific database. The view exists in sys schema.
lyft_recent_statements	Provides list of recent SQL statements that were received and handled by Lyftrondata.