sqlmesh.cli.main

   1from __future__ import annotations
   2
   3import logging
   4import os
   5import sys
   6import typing as t
   7from pathlib import Path
   8
   9import click
  10
  11from sqlmesh import configure_logging, remove_excess_logs
  12from sqlmesh.cli import error_handler
  13from sqlmesh.cli import options as opt
  14from sqlmesh.cli.project_init import (
  15    InitCliMode,
  16    ProjectTemplate,
  17    init_example_project,
  18    interactive_init,
  19)
  20from sqlmesh.core.analytics import cli_analytics
  21from sqlmesh.core.config import load_configs
  22from sqlmesh.core.console import configure_console, get_console
  23from sqlmesh.core.context import Context
  24from sqlmesh.utils import Verbosity
  25from sqlmesh.utils.date import TimeLike
  26from sqlmesh.utils.errors import MissingDependencyError, SQLMeshError
  27
  28logger = logging.getLogger(__name__)
  29
  30
  31SKIP_LOAD_COMMANDS = (
  32    "clean",
  33    "create_external_models",
  34    "destroy",
  35    "environments",
  36    "invalidate",
  37    "janitor",
  38    "migrate",
  39    "rollback",
  40    "run",
  41    "table_name",
  42)
  43SKIP_CONTEXT_COMMANDS = ("init", "ui")
  44LOCAL_ONLY_COMMANDS = ("format",)
  45
  46
  47class _SQLMeshGroup(click.Group):
  48    def parse_args(self, ctx: click.Context, args: t.List[str]) -> t.List[str]:
  49        rest = super().parse_args(ctx, args)
  50        # Preserve the subcommand arguments because Click consumes them before invoking the group callback.
  51        protected_args = getattr(ctx, "_protected_args", None)
  52        if protected_args is None:
  53            protected_args = ctx.protected_args
  54        ctx.meta["subcommand_args"] = tuple(protected_args) + tuple(ctx.args)
  55        return rest
  56
  57
  58def _sqlmesh_version() -> str:
  59    try:
  60        from sqlmesh import __version__
  61
  62        return __version__
  63    except ImportError:
  64        return "0.0.0"
  65
  66
  67@click.group(cls=_SQLMeshGroup, no_args_is_help=True)
  68@click.version_option(version=_sqlmesh_version(), message="%(version)s")
  69@opt.paths
  70@opt.config
  71@click.option(
  72    "--gateway",
  73    type=str,
  74    help="The name of the gateway.",
  75    envvar="SQLMESH_GATEWAY",
  76)
  77@click.option(
  78    "--ignore-warnings",
  79    is_flag=True,
  80    help="Ignore warnings.",
  81    envvar="SQLMESH_IGNORE_WARNINGS",
  82)
  83@click.option(
  84    "--debug",
  85    is_flag=True,
  86    help="Enable debug mode.",
  87)
  88@click.option(
  89    "--log-to-stdout",
  90    is_flag=True,
  91    help="Display logs in stdout.",
  92)
  93@click.option(
  94    "--log-file-dir",
  95    type=str,
  96    help="The directory to write log files to.",
  97)
  98@click.option(
  99    "--dotenv",
 100    type=click.Path(exists=True, path_type=Path),
 101    help="Path to a custom .env file to load environment variables.",
 102    envvar="SQLMESH_DOTENV_PATH",
 103)
 104@click.pass_context
 105@error_handler
 106def cli(
 107    ctx: click.Context,
 108    paths: t.List[str],
 109    config: t.Optional[str] = None,
 110    gateway: t.Optional[str] = None,
 111    ignore_warnings: bool = False,
 112    debug: bool = False,
 113    log_to_stdout: bool = False,
 114    log_file_dir: t.Optional[str] = None,
 115    dotenv: t.Optional[Path] = None,
 116) -> None:
 117    """SQLMesh command line tool."""
 118    if "--help" in sys.argv:
 119        return
 120
 121    configure_logging(
 122        debug,
 123        log_to_stdout,
 124        log_file_dir=log_file_dir,
 125        ignore_warnings=ignore_warnings,
 126    )
 127    configure_console(ignore_warnings=ignore_warnings)
 128
 129    load = True
 130    # Local-only gating must hold for any number of --paths, so it stays outside the block below.
 131    load_state = ctx.invoked_subcommand not in LOCAL_ONLY_COMMANDS
 132    # The parent callback constructs Context before Click invokes `lint`, so inspect its parsed args here.
 133    if ctx.invoked_subcommand == "lint" and "--local" in ctx.meta["subcommand_args"]:
 134        load_state = False
 135
 136    if len(paths) == 1:
 137        path = os.path.abspath(paths[0])
 138        if ctx.invoked_subcommand in SKIP_CONTEXT_COMMANDS:
 139            ctx.obj = path
 140            return
 141        if ctx.invoked_subcommand in SKIP_LOAD_COMMANDS:
 142            load = False
 143
 144    configs = load_configs(config, Context.CONFIG_TYPE, paths, dotenv_path=dotenv)
 145    log_limit = list(configs.values())[0].log_limit
 146
 147    remove_excess_logs(log_file_dir, log_limit)
 148
 149    try:
 150        context = Context(
 151            paths=paths,
 152            config=configs,
 153            gateway=gateway,
 154            load=load,
 155            load_state=load_state,
 156        )
 157    except Exception:
 158        if debug:
 159            logger.exception("Failed to initialize SQLMesh context")
 160        raise
 161
 162    if load and not context.models:
 163        raise click.ClickException(
 164            f"`{paths}` doesn't seem to have any models... cd into the proper directory or specify the path(s) with -p."
 165        )
 166
 167    ctx.obj = context
 168
 169
 170@cli.command("init")
 171@click.argument("engine", required=False)
 172@click.option(
 173    "-t",
 174    "--template",
 175    type=str,
 176    help="Project template. Supported values: dbt, dlt, default, empty.",
 177)
 178@click.option(
 179    "--dlt-pipeline",
 180    type=str,
 181    help="DLT pipeline for which to generate a SQLMesh project. Use alongside template: dlt",
 182)
 183@click.option(
 184    "--dlt-path",
 185    type=str,
 186    help="The DLT pipelines working directory, where DLT stores pipeline state (by default ~/.dlt/pipelines). Use alongside template: dlt",
 187)
 188@click.pass_context
 189@error_handler
 190@cli_analytics
 191def init(
 192    ctx: click.Context,
 193    engine: t.Optional[str] = None,
 194    template: t.Optional[str] = None,
 195    dlt_pipeline: t.Optional[str] = None,
 196    dlt_path: t.Optional[str] = None,
 197) -> None:
 198    """Create a new SQLMesh repository."""
 199    project_template = None
 200    if template:
 201        try:
 202            project_template = ProjectTemplate(template.lower())
 203        except ValueError:
 204            template_strings = "', '".join([template.value for template in ProjectTemplate])
 205            raise click.ClickException(
 206                f"Invalid project template '{template}'. Please specify one of '{template_strings}'."
 207            )
 208
 209    if engine or project_template == ProjectTemplate.DBT:
 210        init_example_project(
 211            path=ctx.obj,
 212            template=project_template or ProjectTemplate.DEFAULT,
 213            engine_type=engine,
 214            pipeline=dlt_pipeline,
 215            dlt_path=dlt_path,
 216        )
 217        return
 218
 219    import sqlmesh.utils.rich as srich
 220
 221    console = srich.console
 222
 223    project_template, engine_type, cli_mode = interactive_init(ctx.obj, console, project_template)
 224
 225    config_path = init_example_project(
 226        path=ctx.obj,
 227        template=project_template,
 228        engine_type=engine_type,
 229        cli_mode=cli_mode or InitCliMode.DEFAULT,
 230        pipeline=dlt_pipeline,
 231        dlt_path=dlt_path,
 232    )
 233
 234    engine_install_text = ""
 235    if engine_type and engine_type not in ("duckdb", "motherduck"):
 236        install_text = (
 237            "pyspark" if engine_type == "spark" else f"sqlmesh\\[{engine_type.replace('_', '')}]"
 238        )
 239        engine_install_text = f'• Run command in CLI to install your SQL engine\'s Python dependencies: pip install "{install_text}"\n'
 240    # interactive init does not support DLT template
 241    next_step_text = {
 242        ProjectTemplate.DEFAULT: f"{engine_install_text}• Update your gateway connection settings (e.g., username/password) in the project configuration file:\n    {config_path}",
 243        ProjectTemplate.DBT: "",
 244    }
 245    next_step_text[ProjectTemplate.EMPTY] = next_step_text[ProjectTemplate.DEFAULT]
 246
 247    quickstart_text = {
 248        ProjectTemplate.DEFAULT: "Quickstart guide:\nhttps://sqlmesh.readthedocs.io/en/stable/quickstart/cli/",
 249        ProjectTemplate.DBT: "dbt guide:\nhttps://sqlmesh.readthedocs.io/en/stable/integrations/dbt/",
 250    }
 251    quickstart_text[ProjectTemplate.EMPTY] = quickstart_text[ProjectTemplate.DEFAULT]
 252
 253    console.print(f"""──────────────────────────────
 254
 255Your SQLMesh project is ready!
 256
 257Next steps:
 258{next_step_text[project_template]}
 259• Run command in CLI: sqlmesh plan
 260• (Optional) Explain a plan: sqlmesh plan --explain
 261
 262{quickstart_text[project_template]}
 263
 264Need help?
 265• Docs:   https://sqlmesh.readthedocs.io
 266• Slack:  https://www.tobikodata.com/slack
 267• GitHub: https://github.com/SQLMesh/sqlmesh/issues
 268""")
 269
 270
 271@cli.command("render")
 272@click.argument("model")
 273@opt.start_time
 274@opt.end_time
 275@opt.execution_time
 276@opt.expand
 277@click.option(
 278    "--dialect",
 279    type=str,
 280    help="The SQL dialect to render the query as.",
 281)
 282@click.option("--no-format", is_flag=True, help="Disable fancy formatting of the query.")
 283@opt.format_options
 284@click.pass_context
 285@error_handler
 286@cli_analytics
 287def render(
 288    ctx: click.Context,
 289    model: str,
 290    start: TimeLike,
 291    end: TimeLike,
 292    execution_time: t.Optional[TimeLike] = None,
 293    expand: t.Optional[t.Union[bool, t.Iterable[str]]] = None,
 294    dialect: t.Optional[str] = None,
 295    no_format: bool = False,
 296    **format_kwargs: t.Any,
 297) -> None:
 298    """Render a model's query, optionally expanding referenced models."""
 299    model = ctx.obj.get_model(model, raise_if_missing=True)
 300
 301    rendered = ctx.obj.render(
 302        model,
 303        start=start,
 304        end=end,
 305        execution_time=execution_time,
 306        expand=expand,
 307    )
 308
 309    format_config = ctx.obj.config_for_node(model).format
 310    format_kwargs = {
 311        **format_config.generator_options,
 312        **{k: v for k, v in format_kwargs.items() if v is not None},
 313    }
 314
 315    sql = rendered.sql(
 316        pretty=True,
 317        dialect=ctx.obj.config.dialect if dialect is None else dialect,
 318        **format_kwargs,
 319    )
 320    if no_format:
 321        print(sql)
 322    else:
 323        ctx.obj.console.show_sql(sql)
 324
 325
 326@cli.command("evaluate")
 327@click.argument("model")
 328@opt.start_time
 329@opt.end_time
 330@opt.execution_time
 331@click.option(
 332    "--limit",
 333    type=int,
 334    help="The number of rows which the query should be limited to.",
 335)
 336@click.pass_context
 337@error_handler
 338@cli_analytics
 339def evaluate(
 340    ctx: click.Context,
 341    model: str,
 342    start: TimeLike,
 343    end: TimeLike,
 344    execution_time: t.Optional[TimeLike] = None,
 345    limit: t.Optional[int] = None,
 346) -> None:
 347    """Evaluate a model and return a dataframe with a default limit of 1000."""
 348    df = ctx.obj.evaluate(
 349        model,
 350        start=start,
 351        end=end,
 352        execution_time=execution_time,
 353        limit=limit,
 354    )
 355    if hasattr(df, "show"):
 356        df.show(limit)
 357    else:
 358        ctx.obj.console.log_success(df)
 359
 360
 361@cli.command("format")
 362@click.argument("paths", nargs=-1)
 363@click.option(
 364    "-t",
 365    "--transpile",
 366    type=str,
 367    help="Transpile project models to the specified dialect.",
 368)
 369@click.option(
 370    "--check",
 371    is_flag=True,
 372    help="Whether or not to check formatting (but not actually format anything).",
 373    default=None,
 374)
 375@click.option(
 376    "--rewrite-casts/--no-rewrite-casts",
 377    is_flag=True,
 378    help="Rewrite casts to use the :: syntax.",
 379    default=None,
 380)
 381@click.option(
 382    "--append-newline",
 383    is_flag=True,
 384    help="Include a newline at the end of each file.",
 385    default=None,
 386)
 387@opt.format_options
 388@click.pass_context
 389@error_handler
 390@cli_analytics
 391def format(
 392    ctx: click.Context, paths: t.Optional[t.Tuple[str, ...]] = None, **kwargs: t.Any
 393) -> None:
 394    """Format all SQL models and audits."""
 395    if not ctx.obj.format(**{k: v for k, v in kwargs.items() if v is not None}, paths=paths):
 396        ctx.exit(1)
 397
 398
 399@cli.command("diff")
 400@click.argument("environment")
 401@click.pass_context
 402@error_handler
 403@cli_analytics
 404def diff(ctx: click.Context, environment: t.Optional[str] = None) -> None:
 405    """Show the diff between the local state and the target environment."""
 406    if ctx.obj.diff(environment, detailed=True):
 407        exit(1)
 408
 409
 410@cli.command("plan")
 411@click.argument("environment", required=False)
 412@opt.start_time
 413@opt.end_time
 414@opt.execution_time
 415@click.option(
 416    "--create-from",
 417    type=str,
 418    help="The environment to create the target environment from if it doesn't exist. Default: prod.",
 419)
 420@click.option(
 421    "--skip-tests",
 422    is_flag=True,
 423    help="Skip tests prior to generating the plan if they are defined.",
 424    default=None,
 425)
 426@click.option(
 427    "--skip-linter",
 428    is_flag=True,
 429    help="Skip linting prior to generating the plan if the linter is enabled.",
 430    default=None,
 431)
 432@click.option(
 433    "--restate-model",
 434    "-r",
 435    type=str,
 436    multiple=True,
 437    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.",
 438)
 439@click.option(
 440    "--no-gaps",
 441    is_flag=True,
 442    help="Ensure that new snapshots have no data gaps when comparing to existing snapshots for matching models in the target environment.",
 443    default=None,
 444)
 445@click.option(
 446    "--skip-backfill",
 447    "--dry-run",
 448    is_flag=True,
 449    help="Skip the backfill step and only create a virtual update for the plan.",
 450    default=None,
 451)
 452@click.option(
 453    "--empty-backfill",
 454    is_flag=True,
 455    help="Produce empty backfill. Like --skip-backfill no models will be backfilled, unlike --skip-backfill missing intervals will be recorded as if they were backfilled.",
 456    default=None,
 457)
 458@click.option(
 459    "--forward-only",
 460    is_flag=True,
 461    help="Create a plan for forward-only changes.",
 462    default=None,
 463)
 464@click.option(
 465    "--allow-destructive-model",
 466    type=str,
 467    multiple=True,
 468    help="Allow destructive forward-only changes to models whose names match the expression.",
 469)
 470@click.option(
 471    "--allow-additive-model",
 472    type=str,
 473    multiple=True,
 474    help="Allow additive forward-only changes to models whose names match the expression.",
 475)
 476@click.option(
 477    "--effective-from",
 478    type=str,
 479    required=False,
 480    help="The effective date from which to apply forward-only changes on production.",
 481)
 482@click.option(
 483    "--no-prompts",
 484    is_flag=True,
 485    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.",
 486    default=None,
 487)
 488@click.option(
 489    "--auto-apply",
 490    is_flag=True,
 491    help="Automatically apply the new plan after creation.",
 492    default=None,
 493)
 494@click.option(
 495    "--no-auto-categorization",
 496    is_flag=True,
 497    help="Disable automatic change categorization.",
 498    default=None,
 499)
 500@click.option(
 501    "--include-unmodified",
 502    is_flag=True,
 503    help="Include unmodified models in the target environment.",
 504    default=None,
 505)
 506@click.option(
 507    "--select-model",
 508    type=str,
 509    multiple=True,
 510    help="Select specific model changes that should be included in the plan.",
 511)
 512@click.option(
 513    "--backfill-model",
 514    type=str,
 515    multiple=True,
 516    help="Backfill only the models whose names match the expression.",
 517)
 518@click.option(
 519    "--no-diff",
 520    is_flag=True,
 521    help="Hide text differences for changed models.",
 522    default=None,
 523)
 524@click.option(
 525    "--run",
 526    is_flag=True,
 527    help="Run latest intervals as part of the plan application (prod environment only).",
 528    default=None,
 529)
 530@click.option(
 531    "--enable-preview",
 532    is_flag=True,
 533    help="Enable preview for forward-only models when targeting a development environment.",
 534    default=None,
 535)
 536@click.option(
 537    "--diff-rendered",
 538    is_flag=True,
 539    help="Output text differences for the rendered versions of the models and standalone audits.",
 540    default=None,
 541)
 542@click.option(
 543    "--explain",
 544    is_flag=True,
 545    help="Explain the plan instead of applying it.",
 546    default=None,
 547)
 548@click.option(
 549    "--ignore-cron",
 550    is_flag=True,
 551    help="Run all missing intervals, ignoring individual cron schedules. Only applies if --run is set.",
 552    default=None,
 553)
 554@click.option(
 555    "--min-intervals",
 556    default=None,
 557    help="For every model, ensure at least this many intervals are covered by a missing intervals check regardless of the plan start date",
 558)
 559@opt.verbose
 560@click.pass_context
 561@error_handler
 562@cli_analytics
 563def plan(
 564    ctx: click.Context,
 565    verbose: int,
 566    environment: t.Optional[str] = None,
 567    **kwargs: t.Any,
 568) -> None:
 569    """Apply local changes to the target environment."""
 570    context = ctx.obj
 571    restate_models = kwargs.pop("restate_model") or None
 572    select_models = kwargs.pop("select_model") or None
 573    allow_destructive_models = kwargs.pop("allow_destructive_model") or None
 574    allow_additive_models = kwargs.pop("allow_additive_model") or None
 575    backfill_models = kwargs.pop("backfill_model") or None
 576    ignore_cron = kwargs.pop("ignore_cron") or None
 577    setattr(get_console(), "verbosity", Verbosity(verbose))
 578
 579    context.plan(
 580        environment,
 581        restate_models=restate_models,
 582        select_models=select_models,
 583        allow_destructive_models=allow_destructive_models,
 584        allow_additive_models=allow_additive_models,
 585        backfill_models=backfill_models,
 586        ignore_cron=ignore_cron,
 587        **kwargs,
 588    )
 589
 590
 591@cli.command("run")
 592@click.argument("environment", required=False)
 593@opt.start_time
 594@opt.end_time
 595@click.option("--skip-janitor", is_flag=True, help="Skip the janitor task.")
 596@click.option(
 597    "--ignore-cron",
 598    is_flag=True,
 599    help="Run for all missing intervals, ignoring individual cron schedules.",
 600)
 601@click.option(
 602    "--select-model",
 603    type=str,
 604    multiple=True,
 605    help="Select specific models to run. Note: this always includes upstream dependencies.",
 606)
 607@click.option(
 608    "--exit-on-env-update",
 609    type=int,
 610    help="If set, the command will exit with the specified code if the run is interrupted by an update to the target environment.",
 611)
 612@click.option(
 613    "--no-auto-upstream",
 614    is_flag=True,
 615    help="Do not automatically include upstream models. Only applicable when --select-model is used. Note: this may result in missing / invalid data for the selected models.",
 616)
 617@click.pass_context
 618@error_handler
 619@cli_analytics
 620def run(ctx: click.Context, environment: t.Optional[str] = None, **kwargs: t.Any) -> None:
 621    """Evaluate missing intervals for the target environment."""
 622    context = ctx.obj
 623    select_models = kwargs.pop("select_model") or None
 624    completion_status = context.run(environment, select_models=select_models, **kwargs)
 625    if completion_status.is_failure:
 626        raise click.ClickException("Run failed.")
 627
 628
 629@cli.command("invalidate")
 630@click.argument("environment", required=True)
 631@click.option(
 632    "--sync",
 633    "-s",
 634    is_flag=True,
 635    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.",
 636)
 637@click.pass_context
 638@error_handler
 639@cli_analytics
 640def invalidate(ctx: click.Context, environment: str, **kwargs: t.Any) -> None:
 641    """Invalidate the target environment, forcing its removal during the next run of the janitor process."""
 642    context = ctx.obj
 643    context.invalidate_environment(environment, **kwargs)
 644
 645
 646@cli.command("janitor")
 647@click.option(
 648    "--ignore-ttl",
 649    is_flag=True,
 650    help="Cleanup snapshots that are not referenced in any environment, regardless of when they're set to expire. Has no effect when --environment is specified.",
 651)
 652@click.option(
 653    "--force-delete",
 654    is_flag=True,
 655    help="Delete expired environment and snapshot state records even when the physical table or view drops fail. "
 656    "Any objects that could not be dropped become orphaned and must be removed manually.",
 657)
 658@click.option(
 659    "--environment",
 660    "-e",
 661    default=None,
 662    help="Scope cleanup to a single expired environment. Global snapshot and interval compaction are skipped.",
 663)
 664@click.pass_context
 665@error_handler
 666@cli_analytics
 667def janitor(
 668    ctx: click.Context,
 669    ignore_ttl: bool,
 670    force_delete: bool,
 671    environment: t.Optional[str],
 672    **kwargs: t.Any,
 673) -> None:
 674    """
 675    Run the janitor process on-demand.
 676
 677    The janitor cleans up old environments and expired snapshots.
 678    """
 679    ctx.obj.run_janitor(ignore_ttl, force_delete=force_delete, environment=environment, **kwargs)
 680
 681
 682@cli.command("destroy")
 683@click.pass_context
 684@error_handler
 685@cli_analytics
 686def destroy(ctx: click.Context, **kwargs: t.Any) -> None:
 687    """
 688    The destroy command removes all project resources.
 689
 690    This includes engine-managed objects, state tables, the SQLMesh cache and any build artifacts.
 691    """
 692    ctx.obj.destroy(**kwargs)
 693
 694
 695@cli.command("dag")
 696@click.argument("file", required=True)
 697@click.option(
 698    "--select-model",
 699    type=str,
 700    multiple=True,
 701    help="Select specific models to include in the dag.",
 702)
 703@click.pass_context
 704@error_handler
 705@cli_analytics
 706def dag(ctx: click.Context, file: str, select_model: t.List[str]) -> None:
 707    """Render the DAG as an html file."""
 708    rendered_dag_path = ctx.obj.render_dag(file, select_model)
 709    if rendered_dag_path:
 710        ctx.obj.console.log_success(f"Generated the dag to {rendered_dag_path}")
 711
 712
 713@cli.command("create_test")
 714@click.argument("model")
 715@click.option(
 716    "-q",
 717    "--query",
 718    "queries",
 719    type=(str, str),
 720    multiple=True,
 721    default=[],
 722    help="Queries that will be used to generate data for the model's dependencies.",
 723)
 724@click.option(
 725    "-o",
 726    "--overwrite",
 727    "overwrite",
 728    is_flag=True,
 729    default=False,
 730    help="When true, the fixture file will be overwritten in case it already exists.",
 731)
 732@click.option(
 733    "-v",
 734    "--var",
 735    "variables",
 736    type=(str, str),
 737    multiple=True,
 738    help="Key-value pairs that will define variables needed by the model.",
 739)
 740@click.option(
 741    "-p",
 742    "--path",
 743    "path",
 744    help=(
 745        "The file path corresponding to the fixture, relative to the test directory. "
 746        "By default, the fixture will be created under the test directory and the file "
 747        "name will be inferred based on the test's name."
 748    ),
 749)
 750@click.option(
 751    "-n",
 752    "--name",
 753    "name",
 754    help="The name of the test that will be created. By default, it's inferred based on the model's name.",
 755)
 756@click.option(
 757    "--include-ctes",
 758    "include_ctes",
 759    is_flag=True,
 760    default=False,
 761    help="When true, CTE fixtures will also be generated.",
 762)
 763@click.pass_obj
 764@error_handler
 765@cli_analytics
 766def create_test(
 767    obj: Context,
 768    model: str,
 769    queries: t.List[t.Tuple[str, str]],
 770    overwrite: bool = False,
 771    variables: t.Optional[t.List[t.Tuple[str, str]]] = None,
 772    path: t.Optional[str] = None,
 773    name: t.Optional[str] = None,
 774    include_ctes: bool = False,
 775) -> None:
 776    """Generate a unit test fixture for a given model."""
 777    obj.create_test(
 778        model,
 779        input_queries=dict(queries),
 780        overwrite=overwrite,
 781        variables=dict(variables) if variables else None,
 782        path=path,
 783        name=name,
 784        include_ctes=include_ctes,
 785    )
 786
 787
 788@cli.command("test")
 789@opt.match_pattern
 790@opt.verbose
 791@click.option(
 792    "--preserve-fixtures",
 793    is_flag=True,
 794    default=False,
 795    help="Preserve the fixture tables in the testing database, useful for debugging.",
 796)
 797@click.argument("tests", nargs=-1)
 798@click.pass_obj
 799@error_handler
 800@cli_analytics
 801def test(
 802    obj: Context,
 803    k: t.List[str],
 804    verbose: int,
 805    preserve_fixtures: bool,
 806    tests: t.List[str],
 807) -> None:
 808    """Run model unit tests."""
 809    result = obj.test(
 810        match_patterns=k,
 811        tests=tests,
 812        verbosity=Verbosity(verbose),
 813        preserve_fixtures=preserve_fixtures,
 814    )
 815    if not result.wasSuccessful():
 816        exit(1)
 817
 818
 819@cli.command("audit")
 820@click.option(
 821    "--model",
 822    "models",
 823    multiple=True,
 824    help="A model to audit. Multiple models can be audited.",
 825)
 826@opt.start_time
 827@opt.end_time
 828@opt.execution_time
 829@click.pass_obj
 830@error_handler
 831@cli_analytics
 832def audit(
 833    obj: Context,
 834    models: t.Iterator[str],
 835    start: TimeLike,
 836    end: TimeLike,
 837    execution_time: t.Optional[TimeLike] = None,
 838) -> None:
 839    """Run audits for the target model(s)."""
 840    if not obj.audit(models=models, start=start, end=end, execution_time=execution_time):
 841        exit(1)
 842
 843
 844@cli.command("check_intervals")
 845@click.option(
 846    "--no-signals",
 847    is_flag=True,
 848    help="Disable signal checks and only show missing intervals.",
 849    default=False,
 850)
 851@click.argument("environment", required=False)
 852@click.option(
 853    "--select-model",
 854    type=str,
 855    multiple=True,
 856    help="Select specific models to show missing intervals for.",
 857)
 858@opt.start_time
 859@opt.end_time
 860@click.pass_context
 861@error_handler
 862@cli_analytics
 863def check_intervals(
 864    ctx: click.Context,
 865    environment: t.Optional[str],
 866    no_signals: bool,
 867    select_model: t.List[str],
 868    start: TimeLike,
 869    end: TimeLike,
 870) -> None:
 871    """Show missing intervals in an environment, respecting signals."""
 872    context = ctx.obj
 873    context.console.show_intervals(
 874        context.check_intervals(
 875            environment,
 876            no_signals=no_signals,
 877            select_models=select_model,
 878            start=start,
 879            end=end,
 880        )
 881    )
 882
 883
 884@cli.command("fetchdf")
 885@click.argument("sql")
 886@click.pass_context
 887@error_handler
 888@cli_analytics
 889def fetchdf(ctx: click.Context, sql: str) -> None:
 890    """Run a SQL query and display the results."""
 891    context = ctx.obj
 892    context.console.log_success(context.fetchdf(sql))
 893
 894
 895@cli.command("info")
 896@click.option(
 897    "--skip-connection",
 898    is_flag=True,
 899    help="Skip the connection test.",
 900)
 901@opt.verbose
 902@click.pass_obj
 903@error_handler
 904@cli_analytics
 905def info(obj: Context, skip_connection: bool, verbose: int) -> None:
 906    """
 907    Print information about a SQLMesh project.
 908
 909    Includes counts of project models and macros and connection tests for the data warehouse.
 910    """
 911    obj.print_info(skip_connection=skip_connection, verbosity=Verbosity(verbose))
 912
 913
 914@cli.command("ui")
 915@click.option(
 916    "--host",
 917    type=str,
 918    default="127.0.0.1",
 919    help="Bind socket to this host. Default: 127.0.0.1",
 920)
 921@click.option(
 922    "--port",
 923    type=int,
 924    default=8000,
 925    help="Bind socket to this port. Default: 8000",
 926)
 927@click.option(
 928    "--mode",
 929    type=click.Choice(["ide", "catalog", "docs", "plan"], case_sensitive=False),
 930    default="ide",
 931    help="Mode to start the UI in. Default: ide",
 932)
 933@click.pass_context
 934@error_handler
 935@cli_analytics
 936def ui(ctx: click.Context, host: str, port: int, mode: str) -> None:
 937    """Start a browser-based SQLMesh UI."""
 938    from sqlmesh.core.console import get_console
 939
 940    get_console().log_warning(
 941        "The UI is deprecated and will be removed in a future version. Please use the SQLMesh VSCode extension instead. "
 942        "Learn more at https://sqlmesh.readthedocs.io/en/stable/guides/vscode/"
 943    )
 944
 945    try:
 946        import uvicorn
 947    except ModuleNotFoundError as e:
 948        raise MissingDependencyError(
 949            "Missing UI dependencies. Run `pip install 'sqlmesh[web]'` to install them."
 950        ) from e
 951
 952    os.environ["PROJECT_PATH"] = ctx.obj
 953    os.environ["UI_MODE"] = mode
 954    if ctx.parent:
 955        config = ctx.parent.params.get("config")
 956        gateway = ctx.parent.params.get("gateway")
 957        if config:
 958            os.environ["CONFIG"] = config
 959        if gateway:
 960            os.environ["GATEWAY"] = gateway
 961    uvicorn.run(
 962        "web.server.app:app",
 963        host=host,
 964        port=port,
 965        log_level="info",
 966        timeout_keep_alive=300,
 967    )
 968
 969
 970@cli.command("migrate")
 971@click.pass_context
 972@error_handler
 973@cli_analytics
 974def migrate(ctx: click.Context) -> None:
 975    """Migrate SQLMesh to the current running version."""
 976    ctx.obj.migrate()
 977
 978
 979@cli.command("rollback")
 980@click.pass_obj
 981@error_handler
 982@cli_analytics
 983def rollback(obj: Context) -> None:
 984    """Rollback SQLMesh to the previous migration."""
 985    obj.rollback()
 986
 987
 988@cli.command("create_external_models")
 989@click.option(
 990    "--strict",
 991    is_flag=True,
 992    help="Raise an error if the external model is missing in the database",
 993)
 994@click.pass_obj
 995@error_handler
 996@cli_analytics
 997def create_external_models(obj: Context, **kwargs: t.Any) -> None:
 998    """Create a schema file containing external model schemas."""
 999    obj.create_external_models(**kwargs)
1000
1001
1002@cli.command("table_diff")
1003@click.argument("source_to_target", required=True, metavar="SOURCE:TARGET")
1004@click.argument("model", required=False)
1005@click.option(
1006    "-o",
1007    "--on",
1008    type=str,
1009    multiple=True,
1010    help="The column to join on. Can be specified multiple times. The model grain will be used if not specified.",
1011)
1012@click.option(
1013    "-s",
1014    "--skip-columns",
1015    type=str,
1016    multiple=True,
1017    help="The column(s) to skip when comparing the source and target table.",
1018)
1019@click.option(
1020    "--where",
1021    type=str,
1022    help="An optional where statement to filter results.",
1023)
1024@click.option(
1025    "--limit",
1026    type=int,
1027    default=20,
1028    help="The limit of the sample dataframe.",
1029)
1030@click.option(
1031    "--show-sample",
1032    is_flag=True,
1033    help="Show a sample of the rows that differ. With many columns, the output can be very wide.",
1034)
1035@click.option(
1036    "-d",
1037    "--decimals",
1038    type=int,
1039    default=3,
1040    help="The number of decimal places to keep when comparing floating point columns. Default: 3",
1041)
1042@click.option(
1043    "--skip-grain-check",
1044    is_flag=True,
1045    help="Disable the check for a primary key (grain) that is missing or is not unique.",
1046)
1047@click.option(
1048    "--warn-grain-check",
1049    is_flag=True,
1050    help="Warn if any selected model is missing a grain, and compute diffs for the remaining models.",
1051)
1052@click.option(
1053    "--temp-schema",
1054    type=str,
1055    help="Schema used for temporary tables. It can be `CATALOG.SCHEMA` or `SCHEMA`. Default: `sqlmesh_temp`",
1056)
1057@click.option(
1058    "--select-model",
1059    "-m",
1060    type=str,
1061    multiple=True,
1062    help="Specify one or more models to data diff. Use wildcards to diff multiple models. Ex: '*' (all models with applied plan diffs), 'demo.model+' (this and downstream models), 'git:feature_branch' (models with direct modifications in this branch only)",
1063)
1064@click.option(
1065    "--schema-diff-ignore-case",
1066    is_flag=True,
1067    help="If set, when performing a schema diff the case of column names is ignored when matching between the two schemas. For example, 'col_a' in the source schema and 'COL_A' in the target schema will be treated as the same column.",
1068)
1069@click.pass_obj
1070@error_handler
1071@cli_analytics
1072def table_diff(
1073    obj: Context, source_to_target: str, model: t.Optional[str], **kwargs: t.Any
1074) -> None:
1075    """Show the diff between two tables or a selection of models when they are specified."""
1076    source, target = source_to_target.split(":")
1077    select_model = kwargs.pop("select_model", None)
1078
1079    if model and select_model:
1080        raise SQLMeshError(
1081            "The --select-model option cannot be used together with a model argument. Please choose one of them."
1082        )
1083
1084    select_models = {model} if model else select_model
1085    obj.table_diff(
1086        source=source,
1087        target=target,
1088        select_models=select_models,
1089        **kwargs,
1090    )
1091
1092
1093@cli.command("rewrite")
1094@click.argument("sql")
1095@click.option(
1096    "--read",
1097    type=str,
1098    help="The input dialect of the sql string.",
1099)
1100@click.option(
1101    "--write",
1102    type=str,
1103    help="The output dialect of the sql string.",
1104)
1105@click.pass_obj
1106@error_handler
1107@cli_analytics
1108def rewrite(obj: Context, sql: str, read: str = "", write: str = "") -> None:
1109    """Rewrite a SQL expression with semantic references into an executable query.
1110
1111    https://sqlmesh.readthedocs.io/en/latest/concepts/metrics/overview/
1112    """
1113    obj.console.show_sql(
1114        obj.rewrite(sql, dialect=read).sql(pretty=True, dialect=write or obj.config.dialect),
1115    )
1116
1117
1118@cli.command("clean")
1119@click.pass_obj
1120@error_handler
1121@cli_analytics
1122def clean(obj: Context) -> None:
1123    """Clears the SQLMesh cache and any build artifacts."""
1124    obj.clear_caches()
1125
1126
1127@cli.command("table_name")
1128@click.argument("model_name", required=True)
1129@click.option(
1130    "--environment",
1131    "--env",
1132    help="The environment to source the model version from.",
1133)
1134@click.option(
1135    "--prod",
1136    is_flag=True,
1137    default=False,
1138    help="If set, return the name of the physical table that will be used in production for the model version promoted in the target environment.",
1139)
1140@click.pass_obj
1141@error_handler
1142@cli_analytics
1143def table_name(
1144    obj: Context,
1145    model_name: str,
1146    environment: t.Optional[str] = None,
1147    prod: bool = False,
1148) -> None:
1149    """Prints the name of the physical table for the given model."""
1150    print(obj.table_name(model_name, environment, prod))
1151
1152
1153@cli.command("dlt_refresh")
1154@click.argument("pipeline", required=True)
1155@click.option(
1156    "-t",
1157    "--table",
1158    type=str,
1159    multiple=True,
1160    help="The specific dlt tables to refresh in the SQLMesh models.",
1161)
1162@click.option(
1163    "-f",
1164    "--force",
1165    is_flag=True,
1166    default=False,
1167    help="If set, existing models are overwritten with the new DLT tables.",
1168)
1169@click.option(
1170    "--dlt-path",
1171    type=str,
1172    help="The DLT pipelines working directory, where DLT stores pipeline state (by default ~/.dlt/pipelines).",
1173)
1174@click.pass_context
1175@error_handler
1176@cli_analytics
1177def dlt_refresh(
1178    ctx: click.Context,
1179    pipeline: str,
1180    force: bool,
1181    table: t.List[str] = [],
1182    dlt_path: t.Optional[str] = None,
1183) -> None:
1184    """Attaches to a DLT pipeline with the option to update specific or all missing tables in the SQLMesh project."""
1185    from sqlmesh.integrations.dlt import generate_dlt_models
1186
1187    sqlmesh_models = generate_dlt_models(ctx.obj, pipeline, list(table or []), force, dlt_path)
1188    if sqlmesh_models:
1189        model_names = "\n".join([f"- {model_name}" for model_name in sqlmesh_models])
1190        ctx.obj.console.log_success(f"Updated SQLMesh project with models:\n{model_names}")
1191    else:
1192        ctx.obj.console.log_success("All SQLMesh models are up to date.")
1193
1194
1195@cli.command("environments")
1196@click.pass_obj
1197@error_handler
1198@cli_analytics
1199def environments(obj: Context) -> None:
1200    """Prints the list of SQLMesh environments with its expiry datetime."""
1201    obj.print_environment_names()
1202
1203
1204@cli.command("lint")
1205@click.option(
1206    "--models",
1207    "--model",
1208    multiple=True,
1209    help="A model to lint. Multiple models can be linted. If no models are specified, every model will be linted.",
1210)
1211@click.option(
1212    "--local",
1213    is_flag=True,
1214    expose_value=False,
1215    help="Lint using only locally loaded project files without loading state.",
1216)
1217@click.pass_obj
1218@error_handler
1219@cli_analytics
1220def lint(
1221    obj: Context,
1222    models: t.Iterator[str],
1223) -> None:
1224    """Run the linter for the target model(s)."""
1225    obj.lint_models(models)
1226
1227
1228@cli.group(no_args_is_help=True)
1229def state() -> None:
1230    """Commands for interacting with state"""
1231    pass
1232
1233
1234@state.command("export")
1235@click.option(
1236    "-o",
1237    "--output-file",
1238    required=True,
1239    help="Path to write the state export to",
1240    type=click.Path(dir_okay=False, writable=True, path_type=Path),
1241)
1242@click.option(
1243    "--environment",
1244    multiple=True,
1245    help="Name of environment to export. Specify multiple --environment arguments to export multiple environments",
1246)
1247@click.option(
1248    "--local",
1249    is_flag=True,
1250    help="Export local state only. Note that the resulting file will not be importable",
1251)
1252@click.option(
1253    "--no-confirm",
1254    is_flag=True,
1255    help="Do not prompt for confirmation before exporting existing state",
1256)
1257@click.pass_obj
1258@error_handler
1259@cli_analytics
1260def state_export(
1261    obj: Context,
1262    output_file: Path,
1263    environment: t.Optional[t.Tuple[str]],
1264    local: bool,
1265    no_confirm: bool,
1266) -> None:
1267    """Export the state database to a file"""
1268    confirm = not no_confirm
1269
1270    if environment and local:
1271        raise click.ClickException("Cannot specify both --environment and --local")
1272
1273    environment_names = list(environment) if environment else None
1274    obj.export_state(
1275        output_file=output_file,
1276        environment_names=environment_names,
1277        local_only=local,
1278        confirm=confirm,
1279    )
1280
1281
1282@state.command("import")
1283@click.option(
1284    "-i",
1285    "--input-file",
1286    help="Path to the state file",
1287    required=True,
1288    type=click.Path(exists=True, dir_okay=False, readable=True, path_type=Path),
1289)
1290@click.option(
1291    "--replace",
1292    is_flag=True,
1293    help="Clear the remote state before loading the file. If omitted, a merge is performed instead",
1294)
1295@click.option(
1296    "--no-confirm",
1297    is_flag=True,
1298    help="Do not prompt for confirmation before updating existing state",
1299)
1300@click.pass_obj
1301@error_handler
1302@cli_analytics
1303def state_import(obj: Context, input_file: Path, replace: bool, no_confirm: bool) -> None:
1304    """Import a state export file back into the state database"""
1305    confirm = not no_confirm
1306    obj.import_state(input_file=input_file, clear=replace, confirm=confirm)