sqlmesh.cli.main

  1from __future__ import annotations
  2
  3import logging
  4import os
  5import sys
  6import typing as t
  7
  8import click
  9
 10from sqlmesh import configure_logging
 11from sqlmesh.cli import error_handler
 12from sqlmesh.cli import options as opt
 13from sqlmesh.cli.example_project import ProjectTemplate, init_example_project
 14from sqlmesh.core.config import load_configs
 15from sqlmesh.core.context import Context
 16from sqlmesh.utils.date import TimeLike
 17from sqlmesh.utils.errors import MissingDependencyError
 18
 19logger = logging.getLogger(__name__)
 20
 21SKIP_LOAD_COMMANDS = ("create_external_models", "migrate", "rollback")
 22
 23
 24def _sqlmesh_version() -> str:
 25    try:
 26        from sqlmesh import __version__
 27
 28        return __version__
 29    except ImportError:
 30        return "0.0.0"
 31
 32
 33@click.group(no_args_is_help=True)
 34@click.version_option(version=_sqlmesh_version(), message="%(version)s")
 35@opt.paths
 36@opt.config
 37@click.option(
 38    "--gateway",
 39    type=str,
 40    help="The name of the gateway.",
 41)
 42@click.option(
 43    "--ignore-warnings",
 44    is_flag=True,
 45    help="Ignore warnings.",
 46)
 47@click.option(
 48    "--debug",
 49    is_flag=True,
 50    help="Enable debug mode.",
 51)
 52@click.option(
 53    "--log-to-stdout",
 54    is_flag=True,
 55    help="Display logs in stdout.",
 56)
 57@click.option(
 58    "--log-file-dir",
 59    type=str,
 60    help="The directory to write log files to.",
 61)
 62@click.pass_context
 63@error_handler
 64def cli(
 65    ctx: click.Context,
 66    paths: t.List[str],
 67    config: t.Optional[str] = None,
 68    gateway: t.Optional[str] = None,
 69    ignore_warnings: bool = False,
 70    debug: bool = False,
 71    log_to_stdout: bool = False,
 72    log_file_dir: t.Optional[str] = None,
 73) -> None:
 74    """SQLMesh command line tool."""
 75    if "--help" in sys.argv:
 76        return
 77
 78    load = True
 79
 80    if len(paths) == 1:
 81        path = os.path.abspath(paths[0])
 82        if ctx.invoked_subcommand in ("init", "ui"):
 83            ctx.obj = path
 84            return
 85        elif ctx.invoked_subcommand in SKIP_LOAD_COMMANDS:
 86            load = False
 87
 88    configs = load_configs(config, Context.CONFIG_TYPE, paths)
 89    log_limit = list(configs.values())[0].log_limit
 90    configure_logging(
 91        debug, ignore_warnings, log_to_stdout, log_limit=log_limit, log_file_dir=log_file_dir
 92    )
 93
 94    try:
 95        context = Context(
 96            paths=paths,
 97            config=configs,
 98            gateway=gateway,
 99            load=load,
100        )
101    except Exception:
102        if debug:
103            logger.exception("Failed to initialize SQLMesh context")
104        raise
105
106    if load and not context.models:
107        raise click.ClickException(
108            f"`{paths}` doesn't seem to have any models... cd into the proper directory or specify the path(s) with -p."
109        )
110
111    ctx.obj = context
112
113
114@cli.command("init")
115@click.argument("sql_dialect", required=False)
116@click.option(
117    "-t",
118    "--template",
119    type=str,
120    help="Project template. Supported values: airflow, dbt, default, empty.",
121)
122@click.pass_context
123@error_handler
124def init(
125    ctx: click.Context, sql_dialect: t.Optional[str] = None, template: t.Optional[str] = None
126) -> None:
127    """Create a new SQLMesh repository."""
128    try:
129        project_template = ProjectTemplate(template.lower() if template else "default")
130    except ValueError:
131        raise click.ClickException(f"Invalid project template '{template}'")
132    init_example_project(ctx.obj, dialect=sql_dialect, template=project_template)
133
134
135@cli.command("render")
136@click.argument("model")
137@opt.start_time
138@opt.end_time
139@opt.execution_time
140@opt.expand
141@click.option(
142    "--dialect",
143    type=str,
144    help="The SQL dialect to render the query as.",
145)
146@click.option("--no-format", is_flag=True, help="Disable fancy formatting of the query.")
147@click.pass_context
148@error_handler
149def render(
150    ctx: click.Context,
151    model: str,
152    start: TimeLike,
153    end: TimeLike,
154    execution_time: t.Optional[TimeLike] = None,
155    expand: t.Optional[t.Union[bool, t.Iterable[str]]] = None,
156    dialect: t.Optional[str] = None,
157    no_format: bool = False,
158) -> None:
159    """Render a model's query, optionally expanding referenced models."""
160    rendered = ctx.obj.render(
161        model,
162        start=start,
163        end=end,
164        execution_time=execution_time,
165        expand=expand,
166    )
167
168    sql = rendered.sql(pretty=True, dialect=ctx.obj.config.dialect if dialect is None else dialect)
169    if no_format:
170        print(sql)
171    else:
172        ctx.obj.console.show_sql(sql)
173
174
175@cli.command("evaluate")
176@click.argument("model")
177@opt.start_time
178@opt.end_time
179@opt.execution_time
180@click.option(
181    "--limit",
182    type=int,
183    help="The number of rows which the query should be limited to.",
184)
185@click.pass_context
186@error_handler
187def evaluate(
188    ctx: click.Context,
189    model: str,
190    start: TimeLike,
191    end: TimeLike,
192    execution_time: t.Optional[TimeLike] = None,
193    limit: t.Optional[int] = None,
194) -> None:
195    """Evaluate a model and return a dataframe with a default limit of 1000."""
196    df = ctx.obj.evaluate(
197        model,
198        start=start,
199        end=end,
200        execution_time=execution_time,
201        limit=limit,
202    )
203    ctx.obj.console.log_success(df)
204
205
206@cli.command("format")
207@click.option(
208    "-t",
209    "--transpile",
210    type=str,
211    help="Transpile project models to the specified dialect.",
212)
213@click.option(
214    "--append-newline",
215    is_flag=True,
216    help="Include a newline at the end of each file.",
217    default=None,
218)
219@click.option(
220    "--normalize",
221    is_flag=True,
222    help="Whether or not to normalize identifiers to lowercase.",
223    default=None,
224)
225@click.option(
226    "--pad",
227    type=int,
228    help="Determines the pad size in a formatted string.",
229)
230@click.option(
231    "--indent",
232    type=int,
233    help="Determines the indentation size in a formatted string.",
234)
235@click.option(
236    "--normalize-functions",
237    type=str,
238    help="Whether or not to normalize all function names. Possible values are: 'upper', 'lower'",
239)
240@click.option(
241    "--leading-comma",
242    is_flag=True,
243    help="Determines whether or not the comma is leading or trailing in select expressions. Default is trailing.",
244    default=None,
245)
246@click.option(
247    "--max-text-width",
248    type=int,
249    help="The max number of characters in a segment before creating new lines in pretty mode.",
250)
251@click.pass_context
252@error_handler
253def format(ctx: click.Context, **kwargs: t.Any) -> None:
254    """Format all SQL models."""
255    ctx.obj.format(**{k: v for k, v in kwargs.items() if v is not None})
256
257
258@cli.command("diff")
259@click.argument("environment")
260@click.pass_context
261@error_handler
262def diff(ctx: click.Context, environment: t.Optional[str] = None) -> None:
263    """Show the diff between the local state and the target environment."""
264    if ctx.obj.diff(environment, detailed=True):
265        exit(1)
266
267
268@cli.command("plan")
269@click.argument("environment", required=False)
270@opt.start_time
271@opt.end_time
272@opt.execution_time
273@click.option(
274    "--create-from",
275    type=str,
276    help="The environment to create the target environment from if it doesn't exist. Default: prod.",
277)
278@click.option(
279    "--skip-tests",
280    is_flag=True,
281    help="Skip tests prior to generating the plan if they are defined.",
282)
283@click.option(
284    "--restate-model",
285    "-r",
286    type=str,
287    multiple=True,
288    help="Restate data for specified models and models downstream from the one specified. For production environment, all related model versions will have their intervals wiped, but only the current versions will be backfilled. For development environment, only the current model versions will be affected.",
289)
290@click.option(
291    "--no-gaps",
292    is_flag=True,
293    help="Ensure that new snapshots have no data gaps when comparing to existing snapshots for matching models in the target environment.",
294)
295@click.option(
296    "--skip-backfill",
297    is_flag=True,
298    help="Skip the backfill step.",
299)
300@click.option(
301    "--forward-only",
302    is_flag=True,
303    help="Create a plan for forward-only changes.",
304    default=None,
305)
306@click.option(
307    "--effective-from",
308    type=str,
309    required=False,
310    help="The effective date from which to apply forward-only changes on production.",
311)
312@click.option(
313    "--no-prompts",
314    is_flag=True,
315    help="Disable interactive prompts for the backfill time range. Please note that if this flag is set and there are uncategorized changes, plan creation will fail.",
316    default=None,
317)
318@click.option(
319    "--auto-apply",
320    is_flag=True,
321    help="Automatically apply the new plan after creation.",
322    default=None,
323)
324@click.option(
325    "--no-auto-categorization",
326    is_flag=True,
327    help="Disable automatic change categorization.",
328    default=None,
329)
330@click.option(
331    "--include-unmodified",
332    is_flag=True,
333    help="Include unmodified models in the target environment.",
334    default=None,
335)
336@click.option(
337    "--select-model",
338    type=str,
339    multiple=True,
340    help="Select specific model changes that should be included in the plan.",
341)
342@click.option(
343    "--backfill-model",
344    type=str,
345    multiple=True,
346    help="Backfill only the models whose names match the expression. This is supported only when targeting a development environment.",
347)
348@click.option(
349    "--no-diff",
350    is_flag=True,
351    help="Hide text differences for changed models.",
352    default=None,
353)
354@click.option(
355    "--run",
356    is_flag=True,
357    help="Run latest intervals as part of the plan application (prod environment only).",
358)
359@click.option(
360    "--enable-preview",
361    is_flag=True,
362    help="Enable preview for forward-only models when targeting a development environment.",
363    default=None,
364)
365@opt.verbose
366@click.pass_context
367@error_handler
368def plan(
369    ctx: click.Context, verbose: bool, environment: t.Optional[str] = None, **kwargs: t.Any
370) -> None:
371    """Apply local changes to the target environment."""
372    context = ctx.obj
373    restate_models = kwargs.pop("restate_model") or None
374    select_models = kwargs.pop("select_model") or None
375    backfill_models = kwargs.pop("backfill_model") or None
376    context.console.verbose = verbose
377    context.plan(
378        environment,
379        restate_models=restate_models,
380        select_models=select_models,
381        backfill_models=backfill_models,
382        **kwargs,
383    )
384
385
386@cli.command("run")
387@click.argument("environment", required=False)
388@opt.start_time
389@opt.end_time
390@click.option("--skip-janitor", is_flag=True, help="Skip the janitor task.")
391@click.option(
392    "--ignore-cron",
393    is_flag=True,
394    help="Run for all missing intervals, ignoring individual cron schedules.",
395)
396@click.pass_context
397@error_handler
398def run(ctx: click.Context, environment: t.Optional[str] = None, **kwargs: t.Any) -> None:
399    """Evaluate missing intervals for the target environment."""
400    context = ctx.obj
401    success = context.run(environment, **kwargs)
402    if not success:
403        raise click.ClickException("Run DAG Failed. See output for details.")
404
405
406@cli.command("invalidate")
407@click.argument("environment", required=True)
408@click.option(
409    "--sync",
410    "-s",
411    is_flag=True,
412    help="Wait for the environment to be deleted before returning. If not specified, the environment will be deleted asynchronously by the janitor process. This option requires a connection to the data warehouse.",
413)
414@click.pass_context
415@error_handler
416def invalidate(ctx: click.Context, environment: str, **kwargs: t.Any) -> None:
417    """Invalidate the target environment, forcing its removal during the next run of the janitor process."""
418    context = ctx.obj
419    context.invalidate_environment(environment, **kwargs)
420
421
422@cli.command("dag")
423@click.argument("file", required=True)
424@click.option(
425    "--select-model",
426    type=str,
427    multiple=True,
428    help="Select specific models to include in the dag.",
429)
430@click.pass_context
431@error_handler
432def dag(ctx: click.Context, file: str, select_model: t.List[str]) -> None:
433    """Render the DAG as an html file."""
434    rendered_dag_path = ctx.obj.render_dag(file, select_model)
435    if rendered_dag_path:
436        ctx.obj.console.log_success(f"Generated the dag to {rendered_dag_path}")
437
438
439@cli.command("create_test")
440@click.argument("model")
441@click.option(
442    "-q",
443    "--query",
444    "queries",
445    type=(str, str),
446    multiple=True,
447    required=True,
448    help="Queries that will be used to generate data for the model's dependencies.",
449)
450@click.option(
451    "-o",
452    "--overwrite",
453    "overwrite",
454    is_flag=True,
455    default=False,
456    help="When true, the fixture file will be overwritten in case it already exists.",
457)
458@click.option(
459    "-v",
460    "--var",
461    "variables",
462    type=(str, str),
463    multiple=True,
464    help="Key-value pairs that will define variables needed by the model.",
465)
466@click.option(
467    "-p",
468    "--path",
469    "path",
470    help=(
471        "The file path corresponding to the fixture, relative to the test directory. "
472        "By default, the fixture will be created under the test directory and the file "
473        "name will be inferred based on the test's name."
474    ),
475)
476@click.option(
477    "-n",
478    "--name",
479    "name",
480    help="The name of the test that will be created. By default, it's inferred based on the model's name.",
481)
482@click.option(
483    "--include-ctes",
484    "include_ctes",
485    is_flag=True,
486    default=False,
487    help="When true, CTE fixtures will also be generated.",
488)
489@click.pass_obj
490@error_handler
491def create_test(
492    obj: Context,
493    model: str,
494    queries: t.List[t.Tuple[str, str]],
495    overwrite: bool = False,
496    variables: t.Optional[t.List[t.Tuple[str, str]]] = None,
497    path: t.Optional[str] = None,
498    name: t.Optional[str] = None,
499    include_ctes: bool = False,
500) -> None:
501    """Generate a unit test fixture for a given model."""
502    obj.create_test(
503        model,
504        input_queries=dict(queries),
505        overwrite=overwrite,
506        variables=dict(variables) if variables else None,
507        path=path,
508        name=name,
509        include_ctes=include_ctes,
510    )
511
512
513@cli.command("test")
514@opt.match_pattern
515@opt.verbose
516@click.option(
517    "--preserve-fixtures",
518    is_flag=True,
519    default=False,
520    help="Preserve the fixture tables in the testing database, useful for debugging.",
521)
522@click.argument("tests", nargs=-1)
523@click.pass_obj
524@error_handler
525def test(
526    obj: Context,
527    k: t.List[str],
528    verbose: bool,
529    preserve_fixtures: bool,
530    tests: t.List[str],
531) -> None:
532    """Run model unit tests."""
533    result = obj.test(
534        match_patterns=k,
535        tests=tests,
536        verbose=verbose,
537        preserve_fixtures=preserve_fixtures,
538    )
539    if not result.wasSuccessful():
540        exit(1)
541
542
543@cli.command("audit")
544@click.option(
545    "--model",
546    "models",
547    multiple=True,
548    help="A model to audit. Multiple models can be audited.",
549)
550@opt.start_time
551@opt.end_time
552@opt.execution_time
553@click.pass_obj
554@error_handler
555def audit(
556    obj: Context,
557    models: t.Iterator[str],
558    start: TimeLike,
559    end: TimeLike,
560    execution_time: t.Optional[TimeLike] = None,
561) -> None:
562    """Run audits for the target model(s)."""
563    obj.audit(models=models, start=start, end=end, execution_time=execution_time)
564
565
566@cli.command("fetchdf")
567@click.argument("sql")
568@click.pass_context
569@error_handler
570def fetchdf(ctx: click.Context, sql: str) -> None:
571    """Run a SQL query and display the results."""
572    context = ctx.obj
573    context.console.log_success(context.fetchdf(sql))
574
575
576@cli.command("info")
577@click.pass_obj
578@error_handler
579def info(obj: Context) -> None:
580    """
581    Print information about a SQLMesh project.
582
583    Includes counts of project models and macros and connection tests for the data warehouse.
584    """
585    obj.print_info()
586
587
588@cli.command("ui")
589@click.option(
590    "--host",
591    type=str,
592    default="127.0.0.1",
593    help="Bind socket to this host. Default: 127.0.0.1",
594)
595@click.option(
596    "--port",
597    type=int,
598    default=8000,
599    help="Bind socket to this port. Default: 8000",
600)
601@click.option(
602    "--mode",
603    type=click.Choice(["ide", "default", "docs", "plan"], case_sensitive=False),
604    default="default",
605    help="Mode to start the UI in. Default: default",
606)
607@click.pass_context
608@error_handler
609def ui(ctx: click.Context, host: str, port: int, mode: str) -> None:
610    """Start a browser-based SQLMesh UI."""
611    try:
612        import uvicorn
613    except ModuleNotFoundError as e:
614        raise MissingDependencyError(
615            "Missing UI dependencies. Run `pip install 'sqlmesh[web]'` to install them."
616        ) from e
617
618    os.environ["PROJECT_PATH"] = ctx.obj
619    os.environ["UI_MODE"] = mode
620    if ctx.parent:
621        config = ctx.parent.params.get("config")
622        gateway = ctx.parent.params.get("gateway")
623        if config:
624            os.environ["CONFIG"] = config
625        if gateway:
626            os.environ["GATEWAY"] = gateway
627    uvicorn.run(
628        "web.server.main:app",
629        host=host,
630        port=port,
631        log_level="info",
632        timeout_keep_alive=300,
633    )
634
635
636@cli.command("migrate")
637@click.pass_context
638@error_handler
639def migrate(ctx: click.Context) -> None:
640    """Migrate SQLMesh to the current running version."""
641    ctx.obj.migrate()
642
643
644@cli.command("rollback")
645@click.pass_obj
646@error_handler
647def rollback(obj: Context) -> None:
648    """Rollback SQLMesh to the previous migration."""
649    obj.rollback()
650
651
652@cli.command("create_external_models")
653@click.pass_obj
654@error_handler
655def create_external_models(obj: Context) -> None:
656    """Create a schema file containing external model schemas."""
657    obj.create_external_models()
658
659
660@cli.command("table_diff")
661@click.argument("source_to_target", required=True, metavar="SOURCE:TARGET")
662@click.argument("model", required=False)
663@click.option(
664    "-o",
665    "--on",
666    type=str,
667    multiple=True,
668    help="The column to join on. Can be specified multiple times. The model grain will be used if not specified.",
669)
670@click.option(
671    "--where",
672    type=str,
673    help="An optional where statement to filter results.",
674)
675@click.option(
676    "--limit",
677    type=int,
678    default=20,
679    help="The limit of the sample dataframe.",
680)
681@click.option(
682    "--show-sample",
683    is_flag=True,
684    help="Show a sample of the rows that differ. With many columns, the output can be very wide.",
685)
686@click.pass_obj
687@error_handler
688def table_diff(
689    obj: Context, source_to_target: str, model: t.Optional[str], **kwargs: t.Any
690) -> None:
691    """Show the diff between two tables."""
692    source, target = source_to_target.split(":")
693    obj.table_diff(
694        source=source,
695        target=target,
696        model_or_snapshot=model,
697        **kwargs,
698    )
699
700
701@cli.command("rewrite")
702@click.argument("sql")
703@click.option(
704    "--read",
705    type=str,
706    help="The input dialect of the sql string.",
707)
708@click.option(
709    "--write",
710    type=str,
711    help="The output dialect of the sql string.",
712)
713@click.pass_obj
714@error_handler
715def rewrite(obj: Context, sql: str, read: str = "", write: str = "") -> None:
716    """Rewrite a SQL expression with semantic references into an executable query.
717
718    https://sqlmesh.readthedocs.io/en/latest/concepts/metrics/overview/
719    """
720    obj.console.show_sql(
721        obj.rewrite(sql, dialect=read).sql(pretty=True, dialect=write or obj.config.dialect),
722    )
723
724
725@cli.command("prompt")
726@click.argument("prompt")
727@click.option(
728    "-e",
729    "--evaluate",
730    is_flag=True,
731    help="Evaluate the generated SQL query and display the results.",
732)
733@click.option(
734    "-t",
735    "--temperature",
736    type=float,
737    help="Sampling temperature. 0.0 - precise and predictable, 0.5 - balanced, 1.0 - creative. Default: 0.7",
738    default=0.7,
739)
740@opt.verbose
741@click.pass_context
742@error_handler
743def prompt(
744    ctx: click.Context, prompt: str, evaluate: bool, temperature: float, verbose: bool
745) -> None:
746    """Uses LLM to generate a SQL query from a prompt."""
747    from sqlmesh.integrations.llm import LLMIntegration
748
749    context = ctx.obj
750
751    llm_integration = LLMIntegration(
752        context.models.values(),
753        context.engine_adapter.dialect,
754        temperature=temperature,
755        verbose=verbose,
756    )
757    query = llm_integration.query(prompt)
758
759    context.console.log_status_update(query)
760    if evaluate:
761        context.console.log_success(context.fetchdf(query))
762
763
764@cli.command("clean")
765@click.pass_obj
766@error_handler
767def clean(obj: Context) -> None:
768    """Clears the SQLMesh cache and any build artifacts."""
769    obj.clear_caches()
770
771
772@cli.command("table_name")
773@click.argument("model_name", required=True)
774@click.option(
775    "--dev",
776    is_flag=True,
777    help="Print the name of the snapshot table used for previews in development environments.",
778    default=False,
779)
780@click.pass_obj
781@error_handler
782def table_name(obj: Context, model_name: str, dev: bool) -> None:
783    """Prints the name of the physical table for the given model."""
784    print(obj.table_name(model_name, dev))