
    w                     r   S r SSKJr  SSKJr  SSKJr  SSKrSSKrSSKrSSKJ	r	  SSK
Jr  SSK
Jr  SSKrSS	KJr  SS
KJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSKJr  SSK J!r!  \RD                  r" " S S\RF                  5      r$g)zThe BigQuery CLI query command.    )absolute_import)division)print_functionN)Optional)app)flags)bigquery_client)bigquery_client_extended)client_data_transfer)
client_job)client_table)utils)bigquery_command)bq_cached_client)utils_data_transfer)utils_flags)utils_formatting)bq_error)bq_id_utils)
stringutilc                      ^  \ rS rSrSrS\S\R                  4U 4S jjrS\	\
   4S jrS\R                  SS	4S
 jrS\R                  SS	4S jr SS\R                  S\SS	4S jjrSrU =r$ )Query)   zquery <sql>namefvc           	      2	  > [         [        U ]  X5        [        R                  " SSSUS9  [        R                  " SSSUS9  [        R
                  " SSS	S
US9  [        R
                  " SSSSUS9  [        R                  " SS SUS9  [        R                  " SS / SQSUS9  [        R                  " SSSUS9  [        R                  " SSSUS9  [        R                  " SS SUS9  [        R                  " SSSUS9  [        R                  " SSSUS9  [        R                  " S S S!US9  [        R                  " S"S S#US9  [        R                  " S$S S%US9  [        R                  " S&S S'US9  [        R                  " S(S S)SS*US+9  [        R                  " S,S S-US9  [        R                  " S.S S/US9  [        R                  " S0S S1US9  [        R
                  " S2S S3US9  [        R
                  " S4S S5US9  [        R                  " S6S S7US9  [        R                  " S8S S9US9  [        R                  " S:S S;US9  [        R                  " S<S S=US9  [        R                  " S>S S?US9  [        R
                  " S@S SAUS9  [        R                  " SBS SCUS9  [        R                  " SDS SEUS9  [        R                  " SFS SGUS9  [        R                  " SHS SIUS9  [        R                  " SJS SKUS9  [        R                  " SLS SMUS9  [        R                  " SNS SOUS9  [        R
                  " SPSSQUS9  [        R
                  " SRSSSTUS9  [        R                  " SUS SVUS9  [        R                  " SWS SXUS9  [        R                  " SYSSZUS9  [        R                  " S[SS\US9  [        R                  " S]S S^US9  [        R                  " S_S S`US9  [        R                  " SaS SbUS9  [        R                  " ScS SdUS9  [        R                  " SeSSfUS9  [        R                  " SgS [        R                  R                  ShUS9  [        R
                  " SiS SjUS9  [        R                   " US9U l        U R%                  U5        g )kNdestination_table z,Name of destination table for query results.)flag_valuesdestination_schemazpSchema for the destination table. Either a filename or a comma-separated list of fields in the form name[:type].	start_rowr   z"First row to return in the result.s)
short_namer   max_rowsd   z&How many rows to return in the result.nbatchzPWhether to run the query in batch mode. Ignored if --priority flag is specified.priority)HIGHINTERACTIVEBATCHzQuery priority. If not specified, priority is determined using the --batch flag. Options include:
 HIGH (only available for whitelisted reservations)
 INTERACTIVE
 BATCHappend_tableFz@When a destination table is specified, whether or not to append.rpcz:If true, use rpc-style query API instead of jobs.insert().
request_idzaThe request_id to use for the jobs.query request. Only valid when used in combination with --rpc.replacez9If true, erase existing contents before loading new data.replace_datazPIf true, erase existing contents only, other table metadata like schema is kept.allow_large_resultsz>Enables larger destination table sizes for legacy SQL queries.dry_runz8Whether the query should be validated without executing.require_cachez6Whether to only run the query if it is already cached.	use_cachezAWhether to use the query cache to avoid rerunning cached queries.min_completion_ratioz[Experimental] The minimum fraction of data that must be scanned before a query returns. If not set, the default server value (1.0) will be used.g      ?)lower_boundupper_boundr   flatten_resultszWhether to flatten nested and repeated fields in the result schema for legacy SQL queries. If not set, the default behavior is to flatten.external_table_definitiona  Specifies a table name and either an inline table definition or a path to a file containing a JSON table definition to use in the query. The format is "table_name::path_to_file_with_json_def" or "table_name::schema@format=uri@connection". For example, "--external_table_definition=Example::/tmp/example_table_def.txt" will define a table named "Example" using the URIs and schema encoded in example_table_def.txt.udf_resourcezuThe URI or local filesystem path of a code file to load and evaluate immediately as a User-Defined Function resource.maximum_billing_tierz.The upper limit of billing tier for the query.maximum_bytes_billedz.The upper limit of bytes billed for the query.use_legacy_sqlzThe choice of using Legacy SQL for the query is optional. If not specified, the server will automatically determine the dialect based on query information, such as dialect prefixes. If no prefixes are found, it will default to Legacy SQL.schema_update_optionaZ  Can be specified when append to a table, or replace a table partition. When specified, the schema of the destination table will be updated with the schema of the new data. One or more of the following options can be specified:
 ALLOW_FIELD_ADDITION: allow new fields to be added
 ALLOW_FIELD_RELAXATION: allow relaxing required fields to nullablelabelz8A label to set on a query job. The format is "key:value"	parametera  Either a file containing a JSON list of query parameters, or a query parameter in the form "name:type:value". An empty name produces a positional parameter. The type may be omitted to assume STRING: name::value or ::value. The value "NULL" produces a null value.time_partitioning_typezEnables time based partitioning on the table and set the type. The default value is DAY, which will generate one partition per day. Other supported values are HOUR, MONTH, and YEAR.time_partitioning_expirationzEnables time based partitioning on the table and sets the number of seconds for which to keep the storage for the partitions in the table. The storage in a partition will have an expiration time of its partition time plus this value.time_partitioning_fieldzEnables time based partitioning on the table and the table will be partitioned based on the value of this field. If time based partitioning is enabled without this value, the table will be partitioned based on the loading time.range_partitioninga  Enables range partitioning on the table. The format should be "field,start,end,interval". The table will be partitioned based on the value of the field. Field must be a top-level, non-repeated INT64 field. Start, end, and interval are INT64 values defining the ranges.require_partition_filterzaWhether to require partition filter for queries over this table. Only apply to partitioned table.clustering_fieldszComma-separated list of field names that specifies the columns on which a table is clustered. To remove the clustering, set an empty value.destination_kms_keyz;Cloud KMS key for encryption of the destination table data.script_statement_timeout_msz4Maximum time to complete each statement in a script.script_statement_byte_budgetz?Maximum bytes that can be billed for any statement in a script.max_statement_resultsz?Maximum number of script statements to display the results for.max_child_jobsi  zMaximum number of child jobs to fetch results from after executing a script.  If the number of child jobs exceeds this limit, only the final result will be displayed.job_timeout_msz&Maximum time to run the entire script.schedulea  Scheduled query schedule. If non-empty, this query requests could create a scheduled query understand the customer project. See https://cloud.google.com/appengine/docs/flexible/python/scheduling-jobs-with-cron-yaml#the_schedule_format for the schedule formatno_auto_schedulingzJCreate a scheduled query configuration with automatic scheduling disabled.display_namez;Display name for the created scheduled query configuration.target_datasetz.Target dataset used to create scheduled query.connection_propertyzConnection propertiescreate_sessionz@Whether to create a new session and run the query in the sesson.
session_idz3An existing session id where the query will be run.
continuousz,Whether to run the query as continuous queryjob_creation_modezAn option on job creation. Options include:
 JOB_CREATION_REQUIRED
 JOB_CREATION_OPTIONAL
 Specifying JOB_CREATION_OPTIONAL may speed up the query if the query engine decides to bypass job creation.	max_slotszfCap on target rate of slot consumption by the query. Requires --alpha=query_max_slots to be specified.)superr   __init__r   DEFINE_stringDEFINE_integerDEFINE_booleanDEFINE_enumDEFINE_floatDEFINE_multi_stringDEFINE_boolDEFINE_enum_classr	   BigqueryClientJobCreationModefrontend_flagsdefine_reservation_id_for_a_jobreservation_id_for_a_job_flag_ProcessCommandRc)selfr   r   	__class__s      %platform/bq/frontend/command_query.pyrX   Query.__init__,   s   	%)	
6	 

	D 
	, 
0 
	 
	

	
  
J	 
D	 
	: 
C	 
	 
H	 
B	 
@	 
K	 
	 	 
	:  
#	,  
	D 
8	 
8	 
B
 
 
	P 
 
B	 
  
 	<  
&	*  
!	1  
	P  
"	+ 
	  
E	 
%>	 
&I	 
I	 
	*  
0	 
	"  
	 

E	 
8	 
t%<" 
J	 
=	 
6	 
&&66	8
 
 
	4 	662F 	& 	2    returnc                    [         R                  " SU5        U R                  U R                  U R                  S.nU R
                  (       ax  0 nU R
                   HX  nUR                  SS5      n[        U5      S:  a  [        R                  " S5      e[        R                  " US   5      X5S   '   MZ     [        U5      US'   U R                  (       a#  [        R                  " U R                  5      US	'   U R                  (       a  U R                  US
'   U R                   (       a  U R                   US'   U R"                  (       a  U R"                  US'   U R$                  b#  [        R&                  " U R$                  5      US'   U R(                  b  U R(                  US'   U R*                  (       a#  [        R,                  " U R*                  5      US'   SR/                  U5      nU(       d  [0        R2                  R5                  5       n[6        R8                  R;                  5       n[<        R>                  R@                  (       a  [<        R>                  R@                  US'   U RB                  US'   [        RD                  " U RF                  U RH                  U RJ                  SU RL                  5      nUb  XS'   [        RN                  " U RP                  5      n	U	(       a  XS'   [        RR                  " U RT                  5      n
U
(       a  XS'   U RV                  (       a'  U RX                  (       d  [        R                  " S5      eSnU RV                  (       a   [Z        R\                  " U RV                  5      nU R^                  (       a  SU R^                  0US'   U R`                  c  U Rb                  bF  U R`                  U Rb                  S.nURe                  5        VVs0 s H  u  pUc  M
  X_M     snnUS'   U Rf                  (       d  U Rh                  (       Ga  URk                  5       nS[Z        Rl                  " US9Rn                  -   nUS-   n URq                  5       Rs                  5       Ru                  US9Rw                  5         U RB                  b  U RB                  (       a  [        R                  " S#5      e[~        R                  " US$U5      n0 nU(       d  [~        R                  " US$U5      n[        R                  " U Rf                  U Rh                  S%9nS&U0nU R                  nU RX                  (       ac  [Z        R                  " XpRX                  S'9R                  5       R                  n[Z        R                  " XpRX                  S'9R                  nUUS('   U R                  (       a  S)US*'   U R                  (       a  S+US*'   U R                  (       a  S,US*'   U RJ                  (       a  U RJ                  US-'   U RF                  (       a  U RF                  US.'   [        R                  " URk                  5       US$UU R                  [        R                  " U5      UU R^                  U[<        R>                  R@                  S/9
n[        S0U-  5        gU R                  (       al  / US1'   U R                   HW  nUR                  S2S5      n[        U5      S:w  a  [        R                  " S35      eUS1   R                  US   US   S4.5        MY     U R                  (       aW  S1U;  a  / US1'   US1    H"  nUS5   S6:X  d  M  [        R                  " S75      e   US1   R                  S6U R                  S4.5        U R                  (       a  U R                  US8'   U R                  R                  (       a  U R                  R@                  US9'   U R                  (       a  U R                  US:'   U R                  b=  [        R                  " [<        R                  R                  5        U R                  US;'   U R                  (       Ga  U R                  (       a  [        R                  " S<5      eU RX                  (       a  [        R                  " S=5      e[        R                  (       d  [        R                  (       a  [        R                  " S>5      eU R                  (       a  [        R                  " S?5      eU R                  (       a  [        R                  " S@5      eU R                  (       a  [        R                  " SA5      eU R                  USB'   U R                  USC'   SDn[         R                  " SEXb5        [        R                  " Xv40 UD6u  nnnU R                  (       a  [        R                  " U5        GO~[6        R                  R                  R                  5       R                  UUUSF9  [        R                  " U5        GO/U RX                  (       a  U R                  (       a  S)US*'   U RX                  (       a  U R                  (       a  S+US*'   U RX                  (       a  U R                  (       a  S,US*'   U R                  (       a  SGUSH'   U R                  (       a  U R                  b  U R                  SI:w  d.  U R                  b7  U R                  (       d&  U R                  SI:X  a  [        R                  " SJ5      eSn U R                  (       a  SIn U R                  b  U R                  n U b  U USK'   U RX                  USL'   U R                  USM'   U R                  USN'   U R                  USO'   [        R                  " 5       USP'   U R                  USB'   [         R                  " SQXb5        [        R                  " Xv40 UD6n!U R                  (       a  [        R                  " U!5        OC[<        R                  R@                  (       d  U R                  U!5        OU R                  UU!5        U(       a=  [        R                  " UR                  [Z        R                  " XpRX                  S'9USR9  ggs  snnf ! [x         a   n[z        R|                  " S S!S"0/ 5      UeSnAff = f)Sa3  Execute a query.

Query should be specified on command line, or passed on stdin.

Examples:
  bq query 'select count(*) from publicdata:samples.shakespeare'
  echo 'select count(*) from publicdata:samples.shakespeare' | bq query

Usage:
  query [<sql_query>]

To cancel a query job, run `bq cancel job_id`.
zIn _Query.RunWithArgs: %s)r2   r4   r5   z::      zMexternal_table_definition parameter is invalid, expected :: as the separator.r   external_table_definitions_jsonudf_resourcesr;   r<   schema_update_optionsNlabelsr.   query_parameters locationr=   time_partitioningrD   
clusteringz;destination_schema can only be used with destination_table.
kmsKeyName$destination_encryption_configuration)statementTimeoutMsstatementByteBudgetscript_optionsz	projects/)id_fallbacksz/dataSources/scheduled_query)r   zScheduled queries are not enabled on this project. Please enable them at https://console.cloud.google.com/bigquery/scheduled-queriesreasonnotFoundzpScheduled queries do not support legacy SQL. Please use standard SQL and set the --use_legacy_sql flag to false.scheduled_query)rM   disable_auto_schedulingquery)r~   
identifierdestination_table_name_templateWRITE_APPENDwrite_dispositionWRITE_TRUNCATEWRITE_TRUNCATE_DATApartitioning_fieldpartitioning_type)
transfer_client	referencedata_sourcerP   rO   params	auth_inforG   schedule_argsrv   z1Transfer configuration '%s' successfully created.connection_properties=z6Invalid connection_property syntax; expected key=value)keyvaluer   rS   zT--session_id should not be set if session_id is specified in --connection_propertiesrR   reservation_idrL   rV   z4allow_large_results cannot be specified in rpc mode.z2destination_table cannot be specified in rpc mode.z>job_id and fingerprint_job_id cannot be specified in rpc mode.z&batch cannot be specified in rpc mode.z0flatten_results cannot be specified in rpc mode.z+continuous cannot be specified in rpc mode.rU   max_resultsFz&Calling client_job.RunQueryRpc(%s, %s)use_full_timestampCREATE_NEVERcreate_dispositionr+   z-Conflicting values of --batch and --priority.r(   r   r1   r8   rT   job_idzCalling client.Query(%s, %s))	apiclientr   schema)yloggingdebugr2   r4   r5   r9   splitlenr   
UsageErrorfrontend_utilsGetExternalDataConfigdictr:   ParseUdfResourcesr;   r<   r>   r?   ParseLabelsr.   r@   ParseParametersjoinsysstdinreadr   ClientGetbq_flagsLOCATIONr   r=   ParseTimePartitioningrA   rB   rC   rE   ParseRangePartitioningrD   ParseClusteringrF   r    r   bq_client_utils
ReadSchemarG   rH   rI   itemsrM   rN   GetTransferV1ApiClientGetProjectReference	projectIdprojectsdataSourcesgetexecute	Exceptionr   BigqueryAccessDeniedErrorr   CheckValidCredsRetrieveAuthorizationInfor   TransferScheduleArgsrP   GetTableReferenceGetDatasetReference	datasetIdtableIdr,   r/   r0   create_transfer_configrO   jsondumpsprintrQ   appendrS   rR   re   presentrL   rV   r   fail_if_not_using_alpha_featureAlphaFeaturesQUERY_MAX_SLOTSr-   r1   FLAGSr   fingerprint_job_idr'   r8   rT   rU   r$   r   RunQueryRpcPrintDryRunInfoFactoryClientTablePrinterGetTablePrinter
PrintTablePrintJobMessagesr3   r(   get_job_id_from_flagsr   SYNCHRONOUS_MODEPrintJobStartInfo_PrintQueryJobResultsr   update_tabler   )"rg   argskwdsexternal_table_defsraw_table_deftable_name_and_defr   clientrw   rD   rx   read_schemar}   r   r   r   r   scheduled_queries_referenceecredentialsr   r   r   rP   r   transfer_name	key_valuerQ   r   fieldsrows	executionr(   jobs"                                     ri   RunWithArgsQuery.RunWithArgs  s
    MM-t4 <<^^ $ 9 9D
 %%99-*00q9!"Q& 
 001CA1FG 	q12 : 155H0Id,-,>>


d?   %)%>%>d!"  %)%>%>d!"  &*&?&?d"#zz%11$**=d8n"??d<~~!/!?!?!OdHHTNEiinne$$((*F!**00d:!00D	&<<##))$$%% $"3'>> #5 //0F0FGJ%<t'='=NN
G  K#..t/F/FGk
006d12 
	)	)	5--9 !% @ @!%!B!Bn ,113 3kd $+3 d }}///557o

-
-6
B
L
Li %.0N$N!  "..044, 	5 	

') 
			$(;(;nn>
 	
 (77
&k i'AA(/
	 +??=="&"9"9m
 5f **n			--#0F0F ! "Y 	 ,==,B,B

' 	 5F01			&4"#	&6"#			&;"#		%	%'+'C'C#$		$	$&*&A&A"#*AA 779''((F#"66%$$**m ?-OP&(d"#//)OOC+	y>QF  	$%,,aL9Q<8	
 0 	 	,(*$%!%&=!>
u%5(  "? "#**
9 #22d))11#AAGGd#22d~~!11

 
 
0
0 ..d;xxx		!	!nnB
 	
 
		nn@
 	
 
11nnL
 	
 
nnEFF			nnOPP	nnJKK"&"8"8d MMd= mm<eJ * 6 6
!!fdI 
&&y1  33CCEPPD-? 	Q 	
 	''	2			D$5$5$2 !			DLL$4 !			D$5$5$9 !			%3!"
**2t}}7O
**
 8PnnLMMh			"==		#Z"&"8"8d$($<$<d ! $ 4 4d??d<"88:d8n"&"8"8dmm2E@V3d3c	&&s+((..s#""63/$$#55!.D.D  }    00J z"
 	s$   *	r#7r#9r) )
s3ssr   Nc                 h    US   S   S   S:X  a  U R                  X5        gU R                  X5        g)a(  Prints the results of a successful query job.

This function is invoked only for successful jobs.  Output is printed to
stdout.  Depending on flags, the output is printed in either free-form or
json style.

Args:
  client: Bigquery client object
  job: json of the job, expressed as a dictionary

statisticsr   statementTypeSCRIPTN)_PrintScriptJobResultsPrintNonScriptQueryJobResults)rg   r   r   s      ri   r   Query._PrintQueryJobResults  s<     	L'"?3	 !!&.
((5rk   c                 6   [        [        R                  " U[        R                  R
                  R                  US   S   S9U R                  S-   SSSSUS   S   S95      nU(       d  U R                  X5        gUR                  S	 S
9  [        U5      U R                  S-   :X  a>  [        R                  R                  SU R                  -  5        U R                  X5        gU Vs/ s H=  nUR                  S0 5      R                  S0 5      R                  SS5      S:X  d  M;  UPM?     nn[        R                   R"                  S:H  nU=(       d    [        R                   R"                  S:H  nU(       a  [        R$                  R                  S5        Sn['        U5       GH  u  p[(        R*                  " U	5      (       a  M#  XpR,                  :  a)  U(       d  [        R$                  R                  S5          GOmU(       aN  US:  aG  U(       a!  [        R$                  R                  S5        GO[        R$                  R                  S5        OU	R                  S0 5      R                  S0 5      R                  S/ 5      n
[        U
5      S::  a    O[        R$                  R                  S[.        R0                  " U
S   R                  SS5      5      -  5        [        U
5      S:  a  [        R$                  R                  S5        U
 H>  n[        R$                  R                  SUR                  SS5      US   US    4-  5        M@     U R                  XUS!9  US-   nGM     U(       a   [        R$                  R                  S"5        ggs  snf )#a  Prints the results of a successful script job.

This function is invoked only for successful script jobs.  Prints the output
of each successful child job representing a statement to stdout.

Child jobs representing expression evaluations are not printed, as are child
jobs which failed, but whose error was handled elsewhere in the script.

Depending on flags, the output is printed in either free-form or
json style.

Args:
  client: Bigquery client object
  job: json of the script job, expressed as a dictionary
jobReferencer   )r   rn   FNjobId)bqclientr   r   	all_usersmin_creation_timemax_creation_time
page_tokenparent_job_idc                     U S   S   $ )Nr   creationTime )r   s    ri   <lambda>.Query._PrintScriptJobResults.<locals>.<lambda>  s    C$5n$Erk   )r   z^Showing only the final result because the number of child jobs exceeds --max_child_jobs (%s).
r   scriptStatisticsevaluationKindr   	STATEMENTr   
prettyjson[r   zaMaximum statement results limit reached. Specify --max_statement_results to increase this limit.
,z,
stackFramesz%s; textro   
z-- at %s[%d:%d]
procedureId	startLinestartColumn)json_escapez]
)listr   ListJobsr   ApiClientHelperProjectReferenceCreaterK   r   sortr   r   stderrwriter   r   FORMATr   stdout	enumerater   IsFailedJobrJ   r   
ensure_str)rg   r   r   
child_jobsstatement_child_jobsis_raw_jsonis_jsonstatements_printedichild_job_infostack_framesstack_frames               ri   r   Query._PrintScriptJobResults  s8   ( !11BBIIn-k: J  ++a/""n-g6	
J  
((5OOEOF
:$--11 
jj- 
 ((5
 C77<$		$	r	" 	   //''61KBX__22lBG	jjs&';<		$	$^	4	4 		99	9
**


 		q5JJS!JJU# |R0S#R(S# 	
 |!


Z**<?+>+>vr+JKK	
 |!
**

4
 'K
**

!//-4k*m, ( ((
g )  .1] =^ 	jju ys   *:N(Nr  c                 :   [         R                  " U5      nUS   S   S   S:H  n[        R                  " U5      (       d  [        R
                  " U5      (       dp  U(       di  Sn[        R                  " UUS   U R                  U R                  S9u  px[        R                  R                  R                  5       R                  XxUS9  O;U(       a4  [        [         R"                  " [        R$                  " U5      5      5        g [        R&                  " U5        g )	Nr   r   r   ASSERTFr   )r!   r$   r   )r   format_job_infor   r  r   IsSuccessfulDmlOrDdlJobr   ReadSchemaAndJobRowsr!   r$   r   r   r   r   r   r   r   r   GetJobMessagesForPrintingr   )	rg   r   r   r  printable_job_infois_assert_jobr   r   r   s	            ri   r   #Query.PrintNonScriptQueryJobResultsA  s     *99#>%g.?8KM'',,667IJJ 
  44

n
NN==	lf 11AACNN
+= O  

**667IJ
  ##$67rk   )re   )F)__name__
__module____qualname____firstlineno__usagestrr   
FlagValuesrX   r   intr   r
   BigqueryClientExtendedr   r   boolr   __static_attributes____classcell__)rh   s   @ri   r   r   )   s    
%c3 cE$4$4 cJd(3- dL	6,CC66*s,CCssr  	%8&==%8 	%8
 %8 %8rk   r   )%__doc__
__future__r   r   r   r   r   r   typingr   abslr   r   r   clientsr	   r
   r   r   r   r   r   frontendr   r   rc   r   r   r   r   r   r   pyglibr   r   BigqueryCmdr   r  rk   ri   <module>rG     st    % &  %   
     # , (    , % % , , (   %   }8(( }8rk   