@Rocky is there an object that i can create on top of existing db tables so that it's like a view but parametrized?

In StarRocks, while there isn't an object explicitly named a "Parameterized View," you can achieve this functionality using several different approaches depending on your use case: 1. Java User-Defined Table Functions (UDTF) This is the most direct way to create a reusable object that behaves like a view but takes parameters. A UDTF is a function that accepts one or more arguments and returns a set of rows (a table). * Pros: Highly reusable across different queries; can be used in the

FROM

clause. * Cons: Requires writing Java code and compiling it into a JAR. * Usage:

sql
    -- Once created and registered
    SELECT * FROM TABLE(my_parameterized_udtf('param_value'));

2. User-Defined Variables (Workaround) For interactive sessions or reports, you can define a standard Logical View that references session-level variables. This mimics a parameterized view by allowing you to "pass" values through the session state. * How it works: Create a view that uses a variable (e.g.,

@my_filter

). * Usage:

sql
    -- 1. Create the view referencing a variable
    CREATE VIEW parameterized_view AS
    SELECT * FROM base_table
    WHERE category = @category_var;

    -- 2. Set the variable in your session
    SET @category_var = 'Electronics';

    -- 3. Query the view
    SELECT * FROM parameterized_view;

3. Prepared Statements (Application Level) If you are querying from an application, the recommended "parameterized" approach is using Prepared Statements (available since v3.2). This allows you to define a SQL template with placeholders and execute it with different values. * Usage:

sql
    PREPARE my_stmt FROM 'SELECT * FROM table WHERE id = ?';
    SET @id = 10;
    EXECUTE my_stmt USING @id;

4. SQL UDFs (Newer Versions) In recent versions (starting from v3.4), StarRocks has added support for SQL UDFs using the

CREATE FUNCTION ... AS <sql_body>

syntax. However, these are currently focused on scalar logic. For returning entire tables (TVFs) using pure SQL, the feature is part of the ongoing roadmap. Summary Recommendation * If you need a system-wide object and are comfortable with Java, use a Java UDTF. * If you need a quick SQL-only solution for internal reporting or ad-hoc tasks, use a Logical View with User-defined Variables. **. * If you are building an application, use Prepared Statements. References • sql-reference/sql-functions/Python_UDF.md • data_source/catalog/iceberg/procedures.md • sql-reference/sql-functions/JAVA_UDF.md • sql-reference/sql-statements/prepared_statement.md • sql-reference/user_defined_variables.md • sql-reference/information_schema/routines.md

@Rocky we are using laravel for our backend application. Currently we are using it to query our db like SomeSummary::where('id', $id) ->select([ 'id', DB::raw('SUM(value) as total_value'), DB::raw('SUM(value2) as total_value2'), ]) ->groupBy('id') ->first(); Currently this SomeSummary is a view I created querying a lot of fact and dimension tables....I want to directly pass this where queries inside the view so it will do the filtering inside the query already

In StarRocks, there is no specific object named "Parameterized View." However, for your Laravel/Eloquent setup, the good news is that StarRocks is designed to handle this automatically through a mechanism called Predicate Pushdown. Here are the ways you can achieve your goal: 1. The Native Way: Standard View + Predicate Pushdown (Recommended) You can continue using a regular Logical View. When you run

SomeSummary::where('id', $id)

, Laravel sends the query:

sql
SELECT id, SUM(value) ... FROM SomeSummary WHERE id = 123 GROUP BY id;

The StarRocks optimizer will push the filter

id = 123

down through the view definition into the underlying fact and dimension tables before the join or aggregation happens. How to verify: Run the query manually in a SQL client with

EXPLAIN

to see if the filter is being applied at the "Scan" level:

sql
EXPLAIN SELECT ... FROM SomeSummary WHERE id = 123;

2. The Performance Way: Asynchronous Materialized Views If your view is very complex and the performance is still slow even with filtering, use an Asynchronous Materialized View (MV). * In StarRocks, MVs can automatically rewrite queries. * Even if you query the original View or base tables, StarRocks can transparently route the query to a pre-computed MV. * This effectively gives you the "instant" performance of a pre-filtered table while keeping the flexibility of a view. 3. The "Hack": User-Defined Variables If you absolutely must "inject" a value into the middle of a view's logic, you can use session variables. 1. Define the view using a variable:

sql
    CREATE VIEW SomeSummary AS
    SELECT ... FROM fact_table
    WHERE tenant_id = @my_tenant_id;

2. In Laravel, set the variable before querying:

php
    DB::statement('SET @my_tenant_id = ?', [$id]);
    $data = SomeSummary::get();

Note: This requires both statements to run on the same database connection/session. 4. Advanced: Java UDTF (Table-Valued Function) StarRocks supports Java User-Defined Table Functions (UDTF). This is the only way to create a true "callable" object that returns a table based on parameters. * Usage:

SELECT * FROM TABLE(my_custom_function(123));

* Cons: This requires writing and compiling a small Java class, which might be overkill for standard Laravel apps. Summary for your Laravel App Stick with the Standard View but ensure your view is "pushdown friendly." Avoid using

LIMIT

or certain complex Window Functions inside the view if you want the

WHERE

clause to reach the bottom-most tables. If performance is the issue, look into Asynchronous Materialized Views for transparent acceleration. References • release_notes/release-3.4.md • sql-reference/sql-functions/Python_UDF.md • sql-reference/user_defined_variables.md

@Rocky is it possible to do this when creating view? Add the BROADCAST hint to the view

Yes, you can absolutely add a

[BROADCAST]

hint (and other join hints) directly inside the

CREATE VIEW

statement. In StarRocks, a view is a logical object. When you query a view, the optimizer expands the view's SQL into your main query. Any hints you include inside the view definition are preserved during this expansion and will guide the optimizer. How to do it When creating your view, place the hint immediately after the

JOIN

keyword:

sql
CREATE VIEW SomeSummary AS
SELECT 
    f.id,
    SUM(f.value) as total_value,
    SUM(f.value2) as total_value2
FROM fact_table f
JOIN [BROADCAST] dimension_table d ON f.dim_id = d.id
GROUP BY f.id;

Why this is useful for your Laravel setup When you call

SomeSummary::where('id', $id)

in Laravel, StarRocks effectively runs:

sql
SELECT * FROM (
    SELECT f.id, ... 
    FROM fact_table f 
    JOIN [BROADCAST] dimension_table d ON f.dim_id = d.id
    GROUP BY f.id
) AS sub 
WHERE id = ?;

1. Hint Enforcement: The

[BROADCAST]

hint forces StarRocks to broadcast the

dimension_table

to all nodes, which is very efficient if the dimension table is small. 2. Predicate Pushdown: Even with the hint, the StarRocks optimizer is smart enough to "push down" your

WHERE id = ?

filter from Laravel into the

fact_table

before the join and aggregation happen. This ensures you aren't scanning the entire table. Important Note on Join Hints When you use a join hint like

[BROADCAST]

, StarRocks disables Join Reordering for that specific join. This means: * The table on the left of the join is treated as the "Large" table (streamed). * The table on the right of the join is treated as the "Small" table (broadcasted). * Make sure your

fact_table

is on the left and

dimension_table

is on the right in your SQL. How to verify it's working You can run an

EXPLAIN

on your query to see if the hint is being respected and if the filter is being pushed down:

sql
EXPLAIN SELECT * FROM SomeSummary WHERE id = 123;

Look for

BROADCAST

in the join type and

PREDICATES

in the scan nodes of your base tables. References • best_practices/query_tuning/query_hint.md • sql-reference/sql-statements/View/CREATE_VIEW.md • faq/Sql_faq.md References • faq/Sql_faq.md • best_practices/query_tuning/query_hint.md • sql-reference/sql-statements/View/CREATE_VIEW.md • using_starrocks/skew_join_v2.md

can runtime filters help here?

Yes, Runtime Filters are a perfect fit for this exact scenario and are one of StarRocks' most powerful features for accelerating joins inside views. While a standard

WHERE

clause tries to filter the table before the join (Predicate Pushdown), Runtime Filters work during the join execution to prune data even further. How Runtime Filters help your View When you query your view in Laravel:

SomeSummary::where('id', $id)

, StarRocks often performs a join between a dimension table and a large fact table. 1. Filter Application: StarRocks first applies your

$id

filter to the dimension table (the small side of the join). 2. Filter Generation: It then creates a "Runtime Filter" (like a Bloom Filter or Min-Max range) based on the resulting IDs from that dimension table. 3. Dynamic Pruning: This filter is sent dynamically to the scan nodes of the large fact table. The fact table then skips reading rows that don't match the filter before they are even sent over the network for the join. Why this is better than just a View In complex views where you have multiple joins, sometimes the SQL optimizer can't "push down" a filter through every single layer of logic. Runtime Filters act as a safety net—they ensure that the "small" side of your join actively restricts the "large" side at the storage level during execution. How to use them In most cases, you don't need to do anything because they are enabled by default. However, you can optimize them for your Laravel application: 1. Ensure they are enabled: If you've changed settings, make sure

enable_global_runtime_filter

is

true

.

sql
    SET GLOBAL enable_global_runtime_filter = true;

2. Combine with Broadcast Joins: Since you've already added the

[BROADCAST]

hint to your view, Runtime Filters will work even better. Broadcast joins use "Local Runtime Filters," which have zero network overhead and are extremely fast. 3. Tuning Types: If you have very high cardinality (millions of unique IDs), you can tell StarRocks to use a Bloom filter for more precision:

sql
    SET runtime_filter_type = 'BLOOM_FILTER';

How to see them in action To verify if your Laravel query is actually using Runtime Filters, run the query with

EXPLAIN ANALYZE

in your SQL console:

sql
EXPLAIN ANALYZE SELECT ... FROM SomeSummary WHERE id = 123;

In the output, look for a section labeled

RuntimeFilter

. It will show you: * How many rows were filtered out at the source. * The type of filter used (Min-Max, Bloom, etc.). Summary Recommendation You don't need to change your Laravel code. By keeping your View + Broadcast Hint and ensuring Runtime Filters are active, StarRocks will handle the "parameterization" by dynamically pruning your fact tables based on the IDs you pass in your Eloquent

where()

clause. References • sql-reference/System_variable.md • using_starrocks/accelerate-cardinality-preserving-joins.md • best_practices/query_tuning/query_hint.md References • knowledge/usage/best-practice.md • administration/management/BE_configuration.md • sql-reference/System_variable.md • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Bitmap_index.md • best_practices/query_tuning/schema_tuning.md