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