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