Fetching data from datastore in native mode, indexing error

[bee99]

google.api_core.exceptions.FailedPrecondition: 400 The query requires an ASC or DESC index for kind company and property company_email. Please use the gcloud CLI to remove the single-property index exemption for this property.

This is the error that I am getting

[ms4446 · Accepted solution]

The error you're encountering in Google Cloud Datastore indicates that your query is attempting to sort data (ascending or descending) based on the company_email property of the company kind, but there isn't an appropriate index to support this operation. In Google Cloud Datastore, indexes are required for most queries that sort or filter data.

To resolve this issue, you need to create an index for the company_email property. Here are the steps to do this:

1. Update Your Index Configuration:

   • You need to define the required index in your index.yaml file. This file specifies how your data should be indexed in Datastore. An entry for the company_email property should look something like this:  

     indexes:
       - kind: company
         properties:
           - name: company_email
             direction: asc  # or desc, depending on your query
     

1. • If you need to support both ascending and descending queries, you'll need two entries – one for asc and another for desc.

2. Deploy the Index:

   • Once you've updated the index.yaml file, deploy it to your Google Cloud project using the gcloud command-line tool. Run the following command in your terminal: 

     gcloud datastore indexes create index

   • This command will create the necessary index in Datastore based on your configuration.

3. Wait for Index to Build:

   • After deploying the index configuration, it might take some time for the index to be fully built and ready for use. You can check the status of your indexes in the Google Cloud Console.

4. Retry Your Query:

   • Once the index is ready, retry your query. It should now work without the indexing error.

5. Remove Single-Property Index Exemption (if applicable):

   • The error message suggests that there might be a single-property index exemption for company_email. If you've previously set up such an exemption, you'll need to remove it. This can be done via the Google Cloud Console or using the gcloud CLI.

6. Review Datastore Indexes Documentation:

   • It's a good idea to review the Datastore Indexes documentation for detailed information on how indexes work and how to configure them properly.

By following these steps, you should be able to resolve the indexing error and successfully execute your query in Google Cloud Datastore.

[ms4446 · Accepted solution]

The error you're encountering in Google Cloud Datastore indicates that your query is attempting to sort data (ascending or descending) based on the company_email property of the company kind, but there isn't an appropriate index to support this operation. In Google Cloud Datastore, indexes are required for most queries that sort or filter data.

To resolve this issue, you need to create an index for the company_email property. Here are the steps to do this:

1. Update Your Index Configuration:

   • You need to define the required index in your index.yaml file. This file specifies how your data should be indexed in Datastore. An entry for the company_email property should look something like this:  

     indexes:
       - kind: company
         properties:
           - name: company_email
             direction: asc  # or desc, depending on your query
     

1. • If you need to support both ascending and descending queries, you'll need two entries – one for asc and another for desc.

2. Deploy the Index:

   • Once you've updated the index.yaml file, deploy it to your Google Cloud project using the gcloud command-line tool. Run the following command in your terminal: 

     gcloud datastore indexes create index

   • This command will create the necessary index in Datastore based on your configuration.

3. Wait for Index to Build:

   • After deploying the index configuration, it might take some time for the index to be fully built and ready for use. You can check the status of your indexes in the Google Cloud Console.

4. Retry Your Query:

   • Once the index is ready, retry your query. It should now work without the indexing error.

5. Remove Single-Property Index Exemption (if applicable):

   • The error message suggests that there might be a single-property index exemption for company_email. If you've previously set up such an exemption, you'll need to remove it. This can be done via the Google Cloud Console or using the gcloud CLI.

6. Review Datastore Indexes Documentation:

   • It's a good idea to review the Datastore Indexes documentation for detailed information on how indexes work and how to configure them properly.

By following these steps, you should be able to resolve the indexing error and successfully execute your query in Google Cloud Datastore.

[bee99]

This index:
Datastore index on 'company' for (company_email DESC)
is not necessary, since single-property indexes are built in. Please remove it from your index file and upgrade to the latest version of the SDK, if you haven't already.
.  Whenever I am trying to deploy the indexes it gives me this error

[bee99]

My Index.yaml file 

indexes:

- kind: company

properties:

- name: company_email

direction: asc # or DESC based on your query requirements

- name: company_name

direction: asc # or DESC based on your query requirements

My Function: 

def get_company_name_by_email(company_email):

client = datastore.Client()

query = client.query(kind='company')

query.projection = ['company_name']

query.add_filter('company_email', '=', company_email)

query.keys_only()

query.limit = 1

results = list(query.fetch())

if results:

company_name = results[0]['company_name']

return company_name

else:

return None

My Usage of this function in views.py 

class UserLoginView(APIView):

def post(self, request):

serializer = UserLoginSerializer(data=request.data)

if serializer.is_valid():

# Assuming serializer.validated_data contains 'email' after successful validation

email = serializer.validated_data.get('email')

# Debugging: Print the email obtained from the serializer

print("Email received:", email)

# Fetch company name associated with the email from the datastore

company_name = get_company_name_by_email(email)

# Authentication successful, return the company name along with the response

return Response({'message': 'Login successful', 'company_name': company_name}, status=status.HTTP_200_OK)

else:

# Authentication failed

return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)

Error: 

Email received: None
/Users/emblemdevice/Desktop/final/Djangobackend/loginapp/datastore_ops.py:9: UserWarning: Detected filter using positional arguments. Prefer using the 'filter' keyword argument instead.
query.add_filter('company_email', '=', company_email)
Internal Server Error: /api/login/
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/grpc_helpers.py", line 75, in error_remapped_callable
return callable_(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/grpc/_channel.py", line 1161, in __call__
return _end_unary_response_blocking(state, call, False, None)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/grpc/_channel.py", line 1004, in _end_unary_response_blocking
raise _InactiveRpcError(state) # pytype: disable=not-instantiable
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with:
status = StatusCode.FAILED_PRECONDITION
details = "The query requires an ASC or DESC index for kind company and property company_email. Please use the gcloud CLI to remove the single-property index exemption for this property."
debug_error_string = "UNKNOWN:Error received from peer ipv4:172.217.17.42:443 {grpc_message:"The query requires an ASC or DESC index for kind company and property company_email. Please use the gcloud CLI to remove the single-property index exemption for this property.", grpc_status:9, created_time:"2023-12-20T12:39:24.022815+00:00"}"
>

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/core/handlers/exception.py", line 55, in inner
response = get_response(request)
^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/core/handlers/base.py", line 197, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/views/decorators/csrf.py", line 65, in _view_wrapper
return view_func(request, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/views/generic/base.py", line 104, in view
return self.dispatch(request, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/rest_framework/views.py", line 509, in dispatch
response = self.handle_exception(exc)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/rest_framework/views.py", line 469, in handle_exception
self.raise_uncaught_exception(exc)
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
raise exc
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/rest_framework/views.py", line 506, in dispatch
response = handler(request, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/emblemdevice/Desktop/final/Djangobackend/loginapp/views.py", line 19, in post
company_name = get_company_name_by_email(email)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/emblemdevice/Desktop/final/Djangobackend/loginapp/datastore_ops.py", line 13, in get_company_name_by_email
results = list(query.fetch())
^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/page_iterator.py", line 208, in _items_iter
for page in self._page_iter(increment=False):
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/page_iterator.py", line 244, in _page_iter
page = self._next_page()
^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/cloud/datastore/query.py", line 814, in _next_page
response_pb = self.client._datastore_api.run_query(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/cloud/datastore_v1/services/datastore/client.py", line 637, in run_query
response = rpc(
^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/gapic_v1/method.py", line 131, in __call__
return wrapped_func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/retry.py", line 366, in retry_wrapped_func
return retry_target(
^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/retry.py", line 204, in retry_target
return target()
^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/timeout.py", line 120, in func_with_timeout
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/google/api_core/grpc_helpers.py", line 77, in error_remapped_callable
raise exceptions.from_grpc_error(exc) from exc
google.api_core.exceptions.FailedPrecondition: 400 The query requires an ASC or DESC index for kind company and property company_email. Please use the gcloud CLI to remove the single-property index exemption for this property.
[20/Dec/2023 12:39:24] "POST /api/login/? HTTP/1.1" 500 155332

[ms4446]

The error you're encountering suggests a conflict between automatic and explicit indexing in your Datastore configuration. Let's resolve this step-by-step:

1. Review index.yaml:

• The explicit company_email index in index.yaml seems unnecessary as Datastore automatically indexes single properties.
• Your query involves both company_email and company_name, requiring a composite index. Update index.yaml to reflect this:

indexes:
- kind: company
  properties:
  - name: company_email
    direction: asc # or desc
  - name: company_name
    direction: asc # or desc

• Remove the single-property index for company_email if it exists.

2. Redeploy Index Configuration:

After updating index.yaml, redeploy it using the gcloud CLI:

gcloud datastore indexes create index.yaml

3. Check for Single-Property Index Exemptions:

The error suggests a possible exemption for company_email. Check and remove it if necessary via the Cloud Console or gcloud CLI.

4. Update Your Function:

• In get_company_name_by_email, remove .keys_only() as you need the company_name.

5. Handle None Email Case:

Ensure the email is correctly passed to the function. A None email might cause unexpected behavior.

6. Debugging:

• If the issue persists, add more logging to pinpoint the failure.

7. SDK Version:

• Ensure you're using the latest versions of the Google Cloud SDK and Datastore library.

[ms4446]

The error message you're encountering suggests that the index you're trying to create for the company_email property in descending order (DESC) on the company kind is redundant. In Google Cloud Datastore, single-property indexes for each property are automatically created for you. This means you don't need to explicitly define an index for just one property unless you have a specific requirement that the automatic indexing doesn't cover.

Here's what you should do:

1. Remove the Redundant Index:

   • Edit your index.yaml file and remove the entry for the single-property index on company_email. Your index.yaml should not include an index definition that only lists company_email.

2. Deploy the Updated Index Configuration:

   • After updating the index.yaml file, deploy it again using the gcloud CLI:

     gcloud datastore indexes create index.yaml

3. Upgrade to the Latest SDK:

   • The error message also suggests upgrading to the latest version of the SDK. Ensure that you are using the latest version of the Google Cloud SDK in your development environment. You can update the SDK using the following command:

     gcloud components update

4. Review Composite Indexes:

   • If your query requires sorting or filtering on multiple properties, you will need a composite index. Make sure your index.yaml file includes definitions for any composite indexes required by your queries, but not for single-property indexes that are automatically created.

5. Redeploy Your Application:

   • After making these changes, redeploy your application to ensure it's using the updated index configuration and the latest SDK.

6. Test Your Queries:

   • Once the updated index configuration is deployed and your application is using the latest SDK, test your queries again. They should work without requiring an explicit single-property index for company_email.

[bee99]

....failed.
ERROR: (gcloud.datastore.indexes.create) INVALID_ARGUMENT: This index:
Datastore index on 'company' for (companyEmail DESC)
is not necessary, since single-property indexes are built in. Please remove it from your index file and upgrade to the latest version of the SDK, if you haven't already.

[ms4446 · Accepted solution]

The error message you're receiving indicates that the index you're trying to create for the company_email property in your index.yaml file is unnecessary. In Datastore, single-property indexes are automatically created for each property of each entity kind. Therefore, you don't need to explicitly define a single-property index for company_email.

Here's what you should do:

1. Update Your index.yaml File:

   • Remove the single-property index for company_email. Since Datastore automatically creates these indexes, explicitly defining them in your index.yaml file is redundant.
   • Your index.yaml should only include definitions for composite indexes (indexes involving multiple properties) that are not automatically created by Datastore.

2. Redeploy the Updated Index Configuration:

   • After updating the index.yaml file, redeploy it using the gcloud CLI:

     gcloud datastore indexes create index.yaml

3. Upgrade to the Latest SDK:

   • Ensure that you are using the latest version of the Google Cloud SDK and the Datastore library in your application. You can update the SDK using the following command:

     gcloud components update

4. Review Your Query:

   • Make sure your query in the application does not require a composite index that is not defined in your index.yaml file. If your query sorts or filters on multiple properties, you need a composite index for those specific property combinations.

5. Test Your Application:

   • After making these changes, test your application again to ensure that the query works as expected without the unnecessary index.

[danielacsantos]

Feito isso ele retorna assim pra mim

ERROR: (gcloud.datastore.indexes.create) INVALID_ARGUMENT: This index:
Datastore index on 'md_top6_promotions' for (idCycle ASC)
is not necessary, since single-property indexes are built in. Please remove it from your index file and upgrade to the latest version of the SDK, if you haven't already.

[ms4446]

O erro que você está encontrando indica que você tentou criar um índice para a propriedade idCycle em ordem ascendente (ASC) para a entidade md_top6_promotions, mas isso é desnecessário porque o Datastore automaticamente cria índices para propriedades únicas em ambas as direções (ASC e DESC). Isso significa que você não precisa definir explicitamente esse índice no seu arquivo index.yaml.

Aqui estão os passos que você deve seguir para resolver este problema:

1. Atualize Seu Arquivo index.yaml

• Abra o seu arquivo index.yaml.

• Remova a definição do índice que especifica a propriedade idCycle para a entidade md_top6_promotions. Isso deve resolver o problema, já que o índice para essa propriedade é criado automaticamente.

Seu arquivo index.yaml deve incluir apenas definições para índices compostos (índices que envolvem múltiplas propriedades) que não são criados automaticamente pelo Datastore.

2. Redeploy da Configuração de Índice Atualizada

Com o arquivo index.yaml atualizado, você não precisa realmente executar o comando gcloud datastore indexes create index.yaml novamente se você apenas removeu o índice desnecessário. O comando é usado para criar índices baseados nas definições do arquivo index.yaml, mas como você está removendo um índice que não deveria estar lá, essa etapa específica pode ser omitida.

3. Atualize para a Versão Mais Recente do SDK

O erro também sugere que você deve garantir que está usando a versão mais recente do SDK do Google Cloud. Isso é importante para evitar problemas de compatibilidade e para garantir que você tenha acesso às funcionalidades mais recentes e correções de bugs.

• Para atualizar o Google Cloud SDK, execute:

gcloud components update

• E, dependendo da linguagem de programação que você está usando, atualize a biblioteca cliente do Datastore. Por exemplo, para Python, use:

pip install --upgrade google-cloud-datastore

4. Teste Sua Aplicação

Depois de fazer essas alterações, teste sua aplicação novamente para

garantir que tudo esteja funcionando conforme esperado. Verifique especialmente as partes da aplicação que realizam consultas ao Datastore, para confirmar que não há mais erros relacionados à configuração de índices.

Considerações Adicionais

• Monitoramento e Otimização: Mesmo após resolver esse problema específico, é uma boa prática monitorar o desempenho das suas consultas ao Datastore e considerar otimizações, especialmente se sua aplicação lida com um volume grande de dados.

• Revisão de Índices Compostos: Se sua aplicação realiza consultas que filtram ou ordenam dados baseados em múltiplas propriedades, você ainda pode precisar definir índices compostos manualmente no seu arquivo index.yaml. Apenas certifique-se de que esses índices são realmente necessários para as consultas específicas que sua aplicação executa.

• Documentação do Datastore: Para qualquer dúvida adicional ou para se aprofundar em aspectos específicos do Google Cloud Datastore, a documentação oficial é um excelente recurso. Ela oferece guias detalhados, melhores práticas e informações sobre gerenciamento de índices.

[danielacsantos]

Isso, essa parte acho que consegui entender, o problema é que quando executo um dataflow que opera em lotes baseado nesse campo, ele critica a ausência desse índice

com.google.datastore.v1.client.DatastoreException: The query requires an ASC or DESC index for kind md_top6_promotions and property idCycle. Please use the gcloud CLI to remove the single-property index exemption for this property., code=FAILED_PRECONDITION


daí pra resolver esse problema, como eu poderia fazer? É um dataflow de firestore bulk delete padrão do gcp, estou usando GQL select __key__ from md_top6_promotion where idCycle = XPTO

Sem o where ele funciona corretamente, então não acho que falte algo na configuração do dataflow, ele deleta todos os registros da tabela com sucesso, já com o where ele critica o erro enviado acima

[ms4446]

The error you are encountering when running Dataflow, which operates in batches and uses a field-based query idCycle, indicates that Datastore is expecting an index for that property, even though, in theory, indexes for single properties are created automatically. This can occur for a few reasons, including project-specific configurations or quirks in the way Dataflow builds and executes queries.

The error message suggests that there is an exemption for automatically creating indexes for the property idCycleon the entity md_top6_promotions, which prevents the query from executing as expected. Here are some steps you can take to try to resolve this issue:

1. Verify Index Configuration in the Google Cloud Console

First of all, check the Google Cloud Console to see if there is any specific index configuration for the entity md_top6_promotionsthat could be causing this behavior. Sometimes custom index settings or exemptions may be applied, which require manual adjustments.

2. Force Index Creation

Although Datastore automatically creates indexes for single properties, in certain cases, like yours, you may need to force index creation manually. This can be done by explicitly adding the index definition to the file index.yamland then deploying that configuration.

Yours index.yamlmight look something like this:

indexes:

  - kind: md_top6_promotions

    properties:

      -name: idCycle

        direction: asc

After adding this definition, you can deploy the index using the command:

gcloud datastore indexes create index.yaml

3. Review the Firestore for Dataflow Documentation

As you are using Dataflow for batch operations in Firestore, it is important to review the documentation specific to these operations. There may be additional requirements or considerations for creating indexes when using Dataflow, especially for query-based bulk deletion operations.

4. Consider Removing Index Exemptions

If there is an index exemption applied to the property idCyclefor the entity md_top6_promotions, you will need to remove that exemption. Check your Google Cloud Datastore or Firestore documentation on how to manage index exemptions, as this may vary depending on updates to the platform.

Resolving index issues with Dataflow can be challenging due to the interaction between multiple parties (Dataflow, Datastore/Firestore, indexes, etc.). Following the steps above should help you diagnose and hopefully resolve the issue. If the issue persists, detailed review of the relevant documentation and consultation with Google Cloud support may provide further direction.

[NoCommandLine]

In addition to the detailed response by @ms4446 , you should also consider first running (testing) your App locally with Cloud Datastore Emulator.

From the above linked documentation

....In addition, the emulator can help you generate indexes for your production Datastore instance and delete unneeded indexes....

This means that when you run your App locally with datastore emulator, and you run the same queries you're going to run in Production (i.e. test your App), datastore emulator will automatically generate any needed indexes and you can then deploy the index file together with your App code.

..... NoCommandLine ......

 https://nocommandline.com

A GUI for Google App Engine
    & Datastore Emulator