How can I find out which DB script these are?

See also questions close to this topic

• Select Customers who purchased one specific Product

  We have two tables:

  1. Customers:

  

  2. Products:

  

  The goal is to select [Id] and [CustomerName] of Customers who purchased Milk AND did not purchase Bread. In the case the correct query should return a customer with Id 2 (Ann).

  The query which I thought of (and which is obviously incorrect) is:

  select CustomerName from dbo.Customers
  where Id in 
  (
    select CustomerId from dbo.Products
    where ProductName = 'Milk' and ProductName != 'Bread'
  )
  

  It returns two customers: 1 (John) and 2 (Ann). How to rewrite the query so it would return only customer with Id 2?
• What is the default key constraint in Mysql or what does the "key" keyword alone do?

  I have create table query that have the something like this:

    create table table_name(
    id int auto_increment,
    ...
    KEY fk_some_name (some_column_name)
    )
  

  What does this "Key" do? I think the person that wrote it didn't know what he was doing. He named it fk_some_name. I think he wanted to create a foreign key. I've searched for a while but I didn't find the usage of Key keyword used alone.
• How do I delete Columns from a table if not another table using SQL?

  Let's say

  Table1 has columns: Column1 Column2 Column3

  Table2 has columns: Column2 Column3 Column4

  I want Column1 to be deleted because it's not in Table2.

  I am guessing I need to a JOIN and then delete from that. I did some searching and found this article: How can I get column names from a table in SQL Server?

  I tried:

  SELECT 
  T.TABLE_NAME AS 'TABLE NAME',
  C.COLUMN_NAME AS 'COLUMN NAME'
  FROM INFORMATION_SCHEMA.TABLES T
  INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
  WHERE T.TABLE_TYPE='BASE TABLE'
  AND T.TABLE_NAME LIKE 'T'

  but I can only get the Column names to show for one Table. I tried modifying it with no luck, and of course I need to delete as well. Even if I could get a list of columns that don't match would help. I am no SQL expert but that's as far as I got. Any help would be appreciated. Thanks!
• Why does my Drupal website throw a HTTP ERROR 500 when I migrate the database?

  I have a Drupal 7.59 website that is currently configured in a Multi-Site configuration and I am trying to break it out of the Multi-Site into and single Drupal configuration.

  I have set up a fresh install of Drupal 7.59, installed fresh copies of all the modules needed, copied over the theme and files and everything works fine until I try to restore the database through the Backup and Migrate module.

  Within less than a minute of trying to upload the database to restore from, it gives a HTTP ERROR 500.

  • Verified that my version of PHP matches the old server.
  • Made sure the version of Drupal 7 is the same as well as all the module versions match up
• Is there any automatic way to normalise google sheet data

  I have a dataset with more than 3000 rows and I have some columns in this format
  ( A / B / C )==Table (1) and I want to normalize each row with specific ID == Table (2):

  
• Problema com count em Query Postgres

  Olá, estou tentando realizar uma Query para identificar a quantidade de veículos que entraram/sairam em um pátio. Estou contando a quantidade de entrada pela quantidade de id, e estou contando a quantidade de saída pelo campo "data_liberacao", quando o campo está nulo quer dizer que a saída não foi feita. O problema é que quando faço o filtro pelo tipo de apreensão "TLV.id is null or TLV.id = 2" ele só considera os veículos com tipo_liberacao null ou do tipo 2. Esse filtro para a quantidade de veículos que saíram está correto, mas está ''comendo'' a quantidade de veículos que entraram, pois só pega os que estão com tipo_liberação null ou tipo 2.

  Minha query :

  SELECT  
      P.DESCRICAO PATIO , 
      TV.DESCRICAO TIPO,  
      count(VA.ID) entrada, 
      count(VA.TIPO_LIBERACAO_ID) liberado, 
      SUM(VA.VALOR_DARE) VALOR,  
      SUM(VA.VALOR_GUINCHO) VALORGUINCHO   
  FROM VEICULO_APREENSAO VA   
      LEFT JOIN TIPO_LIBERACAO_VEICULO TLV ON (TLV.ID = VA.TIPO_LIBERACAO_ID)   
      LEFT JOIN TIPO_VEICULO TV ON (VA.ID_TIPO_VEICULO = TV.ID)      
      LEFT JOIN MOVIMENTACAO_VEICULO MV ON (MV.VEICULO_ID = VA.ID)  
      LEFT JOIN PATIO P ON (P.ID = MV.PATIO_ID_ORIGEM)      
  WHERE   date(data_apreensao)  between '2010-04-01' and '2019-04-16' 
  and (TLV.id is null or TLV.id = 2) and mv.patio_id_origem = 167
  GROUP BY TV.DESCRICAO , P.DESCRICAO   
  order by P.DESCRICAO, TV.DESCRICAO
  
• Boolean Substitution Comaprision Not Giving Proper OUTOPUT

  I have a plsql code in which I have declared variable , but when i change the comparison operator then code is not working properly like as I defined below

  SET SERVEROUTPUT ON;
  DECLARE
      v_num1   number(10);
      v_num2     number(10);
      v_num3      number(10);
      v_num4      number(10);
  
      lowrec      boolean:=FALSE;
  BEGIN   
      v_num3 := 34;
      v_num4:=3;
  
      lowrec :=(v_num3>v_num4);
  
      IF lowrec then
          DBMS_OUTPUT.PUT_LINE('The record is '||v_num3 ||' smaller than '||v_num4 ||' ' ||sys.diutil.bool_to_int(lowrec));
      ELSE
          DBMS_OUTPUT.PUT_LINE('The record is '||v_num3||' higher than '||v_num4 ||' '||sys.diutil.bool_to_int(lowrec));
      END IF;
  
  END;
  
  
  OUTPUT: 
  The record is 34 smaller than 3 1
  
  passed value are 34 and 3 but it is showing 34 is smaller than 3
  
• Two Queries in one copy activity Data Factory v2

  I'm new using Azure Data Factory and i would like to know if there is a mode to run two queries in one activity copy, for example:

  I have a Data Set Oracle for my origin and other for my stage, both use Oracle DB.

  i need copy all registries in a specific query, but before to run it, i need to alter session

  ALTER session SET nls_language = 'AMERICAN';
  

  and immediately run my query

  Select * from ... where  .... 
  

  This is posible? or it's not the way? thks for your answers

  Important: We don't have the posibility to create objects in DB Origin
• Is there a way to search in Oracle using a string if you don't know if the string is a schema/table/etc..?

  I'm trying to search for a particular object in Oracle, but I don't know if it is a table, or schema, or user, etc...

  Is there any command that allows to make a search for string if its type is unknown?

  For example: SQL> SHOW STRING ''
• Convert HQL to Spark

  I'm trying to convert HQL to Spark.

  I have the following query (Works in Hue with Hive editor):

  select reflect('java.util.UUID', 'randomUUID') as id,
      tt.employee, 
      cast( from_unixtime(unix_timestamp (date_format(current_date(),'dd/MM/yyyy HH:mm:ss'), 'dd/MM/yyyy HH:mm:ss')) as timestamp) as insert_date,
      collect_set(tt.employee_detail) as employee_details,
      collect_set( tt.emp_indication ) as employees_indications,
      named_struct ('employee_info', collect_set(tt.emp_info),
          'employee_mod_info', collect_set(tt.emp_mod_info),
          'employee_comments', collect_set(tt.emp_comment) )
          as emp_mod_details,
      from (
          select views_ctr.employee,
          if ( views_ctr.employee_details.so is not null, views_ctr.employee_details, null ) employee_detail,
          if ( views_ctr.employee_info.so is not null, views_ctr.employee_info, null ) emp_info,
          if ( views_ctr.employee_comments.so is not null, views_ctr.employee_comments, null ) emp_comment,
          if ( views_ctr.employee_mod_info.so is not null, views_ctr.employee_mod_info, null ) emp_mod_info,
          if ( views_ctr.emp_indications.so is not null, views_ctr.emp_indications, null ) employees_indication,
          from 
          ( select * from views_sta where emp_partition=0 and employee is not null ) views_ctr
          ) tt
          group by employee
          distribute by employee
  

  First, What I'm trying is to write it in spark.sql as follow:

  sparkSession.sql("select reflect('java.util.UUID', 'randomUUID') as id, tt.employee,    cast( from_unixtime(unix_timestamp (date_format(current_date(),'dd/MM/yyyy HH:mm:ss'), 'dd/MM/yyyy HH:mm:ss')) as timestamp) as insert_date,    collect_set(tt.employee_detail) as employee_details,    collect_set( tt.emp_indication ) as employees_indications,  named_struct ('employee_info', collect_set(tt.emp_info),        'employee_mod_info', collect_set(tt.emp_mod_info),      'employee_comments', collect_set(tt.emp_comment) )      as emp_mod_details, from (      select views_ctr.employee,      if ( views_ctr.employee_details.so is not null, views_ctr.employee_details, null ) employee_detail,     if ( views_ctr.employee_info.so is not null, views_ctr.employee_info, null ) emp_info,      if ( views_ctr.employee_comments.so is not null, views_ctr.employee_comments, null ) emp_comment,       if ( views_ctr.employee_mod_info.so is not null, views_ctr.employee_mod_info, null ) emp_mod_info,      if ( views_ctr.emp_indications.so is not null, views_ctr.emp_indications, null ) employees_indication,      from        ( select * from views_sta where emp_partition=0 and employee is not null ) views_ctr        ) tt        group by employee       distribute by employee")
  

  But I got the following exception:

  Exception in thread "main" org.apache.spark.SparkException: Job aborted due to stage failute: Task not serializable: java.io.NotSerializableException: org.apache.spark.unsafe.types.UTF8String$IntWrapper -object not serializable (class : org.apache.spark.unsafe.types.UTF8String$IntWrapper, value: org.apache.spark.unsafe.types.UTF8String$IntWrapper@30cfd641)

  If I'm trying to run my query without collect_set function its work, It's can fail because struct column types in my table?

  How can I write my HQL query in Spark / fix my exception?
• How to remove spaces in json key in Scala. This is needed for renaming complex datatypes in spark dataframe and store as hive table?

  I need to create a hive table from spark dataframe which has a complex datatype such as an array of structs. I need to parse them as a json and flatten them into a new column.

  As some of the json keys have spaces, I will need to remove them before creating hive table. Is there any generic way to remove spaces in json keys without specifying any schema, so that it can be used for any tables?
• Hive Meta Exception with spark sql response too large

  when I use spark sql query hive table, "MetaException response too large" throw as follow logs, and I want to know why and how to solve, thanks for help, my sql query:

  select job_info_queue_name,jobid,job_info_jobName,job_info_job_status,job_info_job_priority,job_info_submit_time,job_info_finish_time from job_info_database.job_history_v2 where  cluster_name='cluster' and event_day=20190421
  

  Logs:

  Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:response too large, cur:612860282, max:524288000)
      at org.apache.hadoop.hive.ql.metadata.Hive.getAllPartitionsOf(Hive.java:2120)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.spark.sql.hive.client.Shim_v0_13.getPartitionsByFilter(HiveShim.scala:761)
      at org.apache.spark.sql.hive.client.HiveClientImpl$$anonfun$getPartitionsByFilter$1.apply(HiveClientImpl.scala:716)
      at org.apache.spark.sql.hive.client.HiveClientImpl$$anonfun$getPartitionsByFilter$1.apply(HiveClientImpl.scala:714)
      at org.apache.spark.sql.hive.client.HiveClientImpl$$anonfun$withHiveState$1.apply(HiveClientImpl.scala:308)
      at org.apache.spark.sql.hive.client.HiveClientImpl.liftedTree1$1(HiveClientImpl.scala:246)
      at org.apache.spark.sql.hive.client.HiveClientImpl.retryLocked(HiveClientImpl.scala:245)
      at org.apache.spark.sql.hive.client.HiveClientImpl.withHiveState(HiveClientImpl.scala:291)
      at org.apache.spark.sql.hive.client.HiveClientImpl.getPartitionsByFilter(HiveClientImpl.scala:714)
      at org.apache.spark.sql.hive.HiveExternalCatalog$$anonfun$listPartitionsByFilter$1.apply(HiveExternalCatalog.scala:1260)
      at org.apache.spark.sql.hive.HiveExternalCatalog$$anonfun$listPartitionsByFilter$1.apply(HiveExternalCatalog.scala:1253)
      at org.apache.spark.sql.hive.HiveExternalCatalog.withClient(HiveExternalCatalog.scala:97)
      ... 68 more
  Caused by: MetaException(message:response too large, cur:612860282, max:524288000)
      at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_partitions_result$get_partitions_resultStandardScheme.read(ThriftHiveMetastore.java:64537)
      at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_partitions_result$get_partitions_resultStandardScheme.read(ThriftHiveMetastore.java:64495)
      at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_partitions_result.read(ThriftHiveMetastore.java:64429)
      at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:86)
      at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_get_partitions(ThriftHiveMetastore.java:1998)
      at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.get_partitions(ThriftHiveMetastore.java:1983)
      at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitions(HiveMetaStoreClient.java:1066)
      at org.apache.hadoop.hive.metastore.client.impl.CombinedMetastoreClient.listPartitions(CombinedMetastoreClient.java:805)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:152)
      at com.sun.proxy.$Proxy22.listPartitions(Unknown Source)
      at org.apache.hadoop.hive.ql.metadata.Hive.getAllPartitionsOf(Hive.java:2117)
      ... 83 more
  )
  
• How to replace multiple strings in a Teradata select statement

  I need to select from a table, but remove various strings. Let's say entries in my table are:

  DataMart1:Here is some data and other valuable data
  HouseWareMart Other data possibly junk data
  DataLake3 there is nothing here
  

  And I want to remove various strings: "Valuable data" "Other data " "there is nothing "

  So I would get:

  DataMart1:Here is some data and other
  HouseWareMart possibly junk data
  DataLake3 here
  

  I thought about nesting OREPLACE, but couldn't get the syntax right, and searching 'Nested OREPLACE' returned nothing helpful.

  Any suggestions?
• Is it possible to use dask dataframe with teradata python module?

  I have this code:

  import teradata
  import dask.dataframe as dd
  
  login = login
  pwd = password
  
  udaExec = teradata.UdaExec (appName="CAF", version="1.0",
                                      logConsole=False)
  
  session = udaExec.connect(method="odbc", DSN="Teradata",
                                    USEREGIONALSETTINGS='N', username=login,
                                    password=pwd, authentication="LDAP");
  
  

  And the connection is working.

  I want to get a dask dataframe. I have tried this:

  sqlStmt = "SOME SQL STATEMENT"
  
  df = dd.read_sql_table(sqlStmt, session, index_col='id')
  

  And I'm getting this error message:

  AttributeError: 'UdaExecConnection' object has no attribute '_instantiate_plugins'
  

  Does anyone have a suggestion?

  Thanks in advance.
• How to find percentile in a group?

  I have two columns Category_Id and Qty. I want to find min, 25th percentile, 50th percentile, 75th percentile, max group by Category_Id.

  Raw Data - 5940084 rows

  Unique Category Ids - 7084